[R] Change database in SQL Server using RODBC

[Jeff Newmiller]

Please post here using the plain text option in your email software because the HTML is not coming through undamaged.

The first argument to the odbcConnect function is never SQL syntax... for SQL Server it is always a "Data Source Name" (DSN) that has been predefined in the ODBC administration tool (control panel on Windows).

If the databases you are trying to access are managed by the same server and your authentication gives you access to all the databases and tables that you need access to, then you can construct SQL queries that refer to other tables in other databases on that server using the "dot" notation mentioned by Peter, and pass all those queries through the same DSN.

Something like

library(RODBC)
con <- odbcConnect("SQLServer2012")
orders1 <- sqlFetch(con,"sportsDB.dbo.sports")
odbcClose(con)
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On June 30, 2014 7:39:25 AM PDT, Ira Sharenow <irasharenow100 at yahoo.com> wrote:
>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]]
>
>
>
>------------------------------------------------------------------------
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.