[R] Reading SQL Server Tables using RODBC

[Marc Schwartz]

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