Change database in SQL Server using RODBC
Thanks for everyone?s help. I followed the instruction given on a variety of web pages in order to set up the connection. The problem is trying to use the first connection for a second database and doing so from within R. It seems to me that an easy workaround is to simply set up another connection and use a second database as the default. In Windows 7 the basic strategy is do the following: Control Panel Administrative Tools Data Sources (ODBC) In the ODBC Data Source Administrator pop up select SQL 2012 and then click on Add. Since I do not have to work with a large number of databases, I consider this to be a satisfactory work around.
On 6/30/2014 8:17 AM, Frede Aakmann T?gersen wrote:
Hi
I can see that you do have troubles understanding how all this works
using the RODBC package. Peter wasn't really being helpful to you.
This is something that is quite difficult to help with not sitting
beside you. Do you not having some local help from e.g. the IT department?
However for a start please let me know how you managed to get
con = odbcConnect("SQLServer2012")
to work.
It seems like that some DSN was set up.
From there we can probably find a solution.
Br. Frede
Sendt fra Samsung mobil
-------- Oprindelig meddelelse --------
Fra: Ira Sharenow
Dato:30/06/2014 16.42 (GMT+01:00)
Til: Peter Crowther ,R list
Emne: Re: [R] Change database in SQL Server using RODBC
Thanks for everyone?s feedback.
library(RODBC)
con = odbcConnect("SQLServer2012")
orders1 = sqlFetch(con,"dbo.orders")
odbcClose(con)
Allowed me to close the connection properly. Thanks.
However, I still cannot figure out how to connect to the second database
and table.
library(RODBC)
con2 = odbcConnect("[sportsDB].dbo.sports")
Warning messages:
1: In odbcDriverConnect("DSN=[sportsDB].dbo.sports") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=[sportsDB].dbo.sports") :
ODBC connection failed
con2 = odbcConnect("[sportsDB].[dbo].sports")
Warning messages:
1: In odbcDriverConnect("DSN=[sportsDB].[dbo].sports") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=[sportsDB].[dbo].sports") :
ODBC connection failed
con2 = odbcConnect("[sportsDB].[dbo].[sports]")
Warning messages:
1: In odbcDriverConnect("DSN=[sportsDB].[dbo].[sports]") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=[sportsDB].[dbo].[sports]") :
ODBC connection failed
con3 = odbcConnect("SQLServer2012")
orders3 = sqlFetch(con3, "sportsDB.dbo.sports")
Error in odbcTableExists(channel, sqtable) : ?sportsDB.dbo.sports?: table not found on channel On 6/30/2014 1:34 AM, Peter Crowther wrote:
On 30 June 2014 02:44, Ira Sharenow <irasharenow100 at yahoo.com> wrote:
I wish to query tables that are NOT in the default SQL Server 2012
database.
Now for the problem. I also want to read in the table dbo.sports. That table is in the database sportsDB. I did not see any way to do so from within R.
Can you not use sportsDB.dbo.sports to reference the table? In general, table reference syntax is [ [ [ serverName '.' ] databaseName '.' ] [schema ] '.' ] tableName, where the names need only be surrounded by [...] if they are not valid SQL Server identifiers. Many people may suggest you reference [sportsDB].[dbo].[sports]; this is unnecessary verbiage. Cheers, - Peter
[[alternative HTML version deleted]]