[R] Change database in SQL Server using RODBC

[Frede Aakmann Tøgersen]

Hi

See inline below for my comments.

Yours sincerely / Med venlig hilsen


Frede Aakmann Tøgersen
Specialist, M.Sc., Ph.D.
Plant Performance & Modeling

Technology & Service Solutions
T +45 9730 5135
M +45 2547 6050
frtog at vestas.com
http://www.vestas.com

Company reg. name: Vestas Wind Systems A/S
This e-mail is subject to our e-mail disclaimer statement.
Please refer to www.vestas.com/legal/notice
If you have received this e-mail in error please contact the sender. 

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
> On Behalf Of Ira Sharenow
> Sent: 30. juni 2014 03:45
> To: r-help at r-project.org
> Subject: [R] Change database in SQL Server using RODBC
> 
> I wish to query tables that are NOT in the default SQL Server 2012 database.
> 
> At work I am using SQL Server 2012 and Windows 7. I tested the following
> on my home set up of Server 2012 and Windows 7. I am using RStudio.
> 
> I wish to connect to several SQL Server 2012 databases from R.
> 
> This page helped me get started.
> 
> http://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-
> 2012-and-14/
> 
> 
> I set up my connection with TSQLFundamentals2008 as the default database.
> 
> I then read in the table dbo.orders with the following code.
> 
> > library(RODBC)
> 
> > con = odbcConnect("SQLServer2012")
> 
> > orders1 = sqlFetch(con,"dbo.orders")
> 
> > odbcClose("SQLServer2012")
> 
> Error in odbcClose("SQLServer2012") :
> 
> argument is not an open RODBC channel
> 

"SQLServer2012" is not a connection but the name of your DSN (data source name) you probably created using Windows tools.

Instead do a odbcClose(con) where con is the connection you opened.

> >
> 
> > head(orders1)
> 
> It appears to have worked properly. But I do not know what the error
> message means.
> 
> 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.
> 

Since I don't how you made your DSN I don't know which database you connect to, but it must be a database which holds dbo.orders since you can query that table. 

If dbo.sports is not in the same database but in the sportsDB database one way to be able to query that is to make a DSN for that database as you did for your first DSN.

Perhaps a query like this "select * from [sportsDB].[dbo].[sports]" using the con connection could also work. 


> I could not find the answer in ODBC Connectivity by Brian Ripley,
> November 25, 2013.
> 
> Any ideas?
> 
> Are there alternative strategies such as using dplyr or data.table? The
> work tables may have hundreds of thousands of rows.
> 
> Thanks.
> 
> 
> 	[[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.