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>> 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. 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]]
Frede Aakmann Tøgersen
2014-Jun-30 05:17 UTC
[R] Change database in SQL Server using RODBC
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.
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