[R] Change database in SQL Server using RODBC

[Ira Sharenow]

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]]
>