Hello Everyone,
I'm still quite new to R and am having trouble reading SQL Server Tables
using RODBC. I've looked though the RODBC documentation as well as material
I found online and in the book "Data Manipulation with R," but I just
can't quite seem to get things to work.
Right now, I have code that looks something like:
channel <- odbcDriverConnect
("Driver=SQL Server;
Server=Fred; Connection=xx.x.x.xxx
Database=mydatabasename;
uid=pmiller; pwd=password;
Trusted_Connection=FALSE;")
When I run this code, I get the following error:
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified
I do seem to be able to make a connection if I use:
channel <- odbcDriverConnect("driver=SQL Server; server=Fred")
Here, I haven't specified enough information but am allowed to do so
subsequently using the SQL Server Login.
Can anyone show me how to rewrite the code above to make the connection I need
without using the SQL Server Login?
Thanks,
Paul
__________________________________________________________________
Looking for the perfect gift? Give the gift of Flickr!
[[alternative HTML version deleted]]
Paul,
I have never tried odbcDriverConnect, but I can tell you how I connect to my SQL
Server 2005 db. I create a DNS entry using "odbcad32" (use
start>run). You can specify everything there and test the connection. Once
that tests okay, just use the following:
library(RODBC)
xf <- odbcConnect("dns entry name", "sql username",
"sql user password")
HTH,
Roger
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of Paul Miller
Sent: Thursday, March 25, 2010 12:47 PM
To: r-help at r-project.org
Subject: [R] Reading SQL Server Tables using RODBC
Hello Everyone,
I'm still quite new to R and am having trouble reading SQL Server Tables
using RODBC. I've looked though the RODBC documentation as well as material
I found online and in the book "Data Manipulation with R," but I just
can't quite seem to get things to work.
Right now, I have code that looks something like:
channel <- odbcDriverConnect
("Driver=SQL Server;
Server=Fred; Connection=xx.x.x.xxx
Database=mydatabasename;
uid=pmiller; pwd=password;
Trusted_Connection=FALSE;")
When I run this code, I get the following error:
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified
I do seem to be able to make a connection if I use:
channel <- odbcDriverConnect("driver=SQL Server; server=Fred")
Here, I haven't specified enough information but am allowed to do so
subsequently using the SQL Server Login.
Can anyone show me how to rewrite the code above to make the connection I need
without using the SQL Server Login?
Thanks,
Paul
__________________________________________________________________
Looking for the perfect gift? Give the gift of Flickr!
[[alternative HTML version deleted]]
***************************************************************
This message is for the named person's use only. It may\...{{dropped:20}}
On Mar 25, 2010, at 11:46 AM, Paul Miller wrote:> Hello Everyone, > > I'm still quite new to R and am having trouble reading SQL Server Tables using RODBC. I've looked though the RODBC documentation as well as material I found online and in the book "Data Manipulation with R," but I just can't quite seem to get things to work. > > Right now, I have code that looks something like: > > channel <- odbcDriverConnect > ("Driver=SQL Server; > Server=Fred; Connection=xx.x.x.xxx > Database=mydatabasename; > uid=pmiller; pwd=password; > Trusted_Connection=FALSE;") > > When I run this code, I get the following error: > > [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified > > I do seem to be able to make a connection if I use: > > channel <- odbcDriverConnect("driver=SQL Server; server=Fred") > > Here, I haven't specified enough information but am allowed to do so subsequently using the SQL Server Login. > > Can anyone show me how to rewrite the code above to make the connection I need without using the SQL Server Login? > > Thanks, > > Paul >If you have not, you should read: vignette("RODBC") which provides important information, in addition to the package manual. I am not on Windows, nor do I use SQL Server, however the errors above would suggest that you have not specified either a user or system DSN, which is done via the ODBC configuration and is covered in the above vignette, specifically in Appendix B. Typically, calling odbcConnect() with the appropriate parameters is easier to use. If the ODBC driver configuration for SQL Server allows you to specify a user name and password, you can perhaps include them there, which may then allow you to make the RODBC connection without having to specify those two parameters. However, there is a potential security risk in doing so, depending upon whether you specify a user or system DSN, which then may be available to others with access to your computer. HTH, Marc Schwartz
Paul Miller wrote:> > Hello Everyone, > ? > I'm still quite new to R and am having trouble reading SQL Server Tables > using RODBC. I've looked though the RODBC documentation as well as > material I found online and in the book "Data Manipulation with R," but I > just can't quite seem to get things to work. > ? > Right now, I have code that looks something like: > ? > channel <- odbcDriverConnect > ("Driver=SQL Server;? > Server=Fred; Connection=xx.x.x.xxx > Database=mydatabasename;? > uid=pmiller; pwd=password;? > Trusted_Connection=FALSE;") > ? > When I run this code, I get the following error: > ? > [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver > Manager] Data source name not found and no default driver specified > ? > I do seem to be able to make a connection if I use: > ? > channel <- odbcDriverConnect("driver=SQL Server; server=Fred") > ? > Here, I haven't specified enough information but am allowed to do so > subsequently using the SQL Server Login. > ? > Can anyone show me how to?rewrite the code above to make the connection I > need without using the SQL Server Login? > ? > Thanks, > ? > Paul > ? > ? > Hi Paul M, > > I had the same problem, and based on your question & the piece that you > got to work I was able to figure the rest of it out. Here's how I got it > to work: > > channel <- odbcDriverConnect("driver=SQL Server; > server=Fred";uid=pmiller;pwd=password") > > and that makes the connection without having to manually type in the uid & > pwd via the dialog box. > > Hope that helps you, and thanks for helping me figure out the first half! > > Paul F > > ? > > > __________________________________________________________________ > Looking for the perfect gift? Give the gift of Flickr! > > > [[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. > >-- View this message in context: http://r.789695.n4.nabble.com/Reading-SQL-Server-Tables-using-RODBC-tp1690973p3081141.html Sent from the R help mailing list archive at Nabble.com.