RODBC not recognizing my odbc settings
I'm running R 2.15.2 on a Red Hat Linux 6 server. My goal is to connect to a MS SQL Server Database on another machine via RODBC. I did my research and downloaded and installed the linux version of the MS SQL ODBC driver from the microsoft support website. I to had build unixODBC version 2.3.0 from source, because it is required by the windows driver and is not in the RHL repos yet (the repo version is 2.2.14).
Anyhow, after a bit of work, I finally got the driver installed and configured properly, and I can connect successfully to the SQL Server database via an isql command:
$ isql -v test testuser testpass
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
so I know I have my odbc.ini and odbcinst.ini files properly set up.
However, when I try to access the ODBC connection from within R, the following happens:
> test <- odbcDriverConnect('DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass')
Warning messages:
1: In odbcDriverConnect("DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass") :
[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data
source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=test;Database=RSQLTest01;Uid=testuser;Pwd=testpass") :
ODBC connection failed
Here's the result of the odbcDataSources command in R:
> odbcDataSources()
named character(0)
I have been doing some research and I think the solution (though I could be wrong) may have something to do with properly configuring the ODBC environment variables so that RODBC knows where to go to find odbc.ini. Based on my research, I found the following environment variables that may be relevant: $ODBCINI, $ODBCSYSINI, $ODBC_ROOT $ODBC_INCLUDE, and $ODBC_LIBS. I have a reasonable idea of what these should be set to, but I'm not sure how to set them permanently, and so that RODBC can recognize where to find the appropriate files.
Can anyone shed some light on this for me? I'm not sure I properly understand how environment variables work in linux, and particularly why isql has no trouble connecting, but RODBC can't even find the driver/data source.
NOTE: when I had the repo version of unixODBC installed, RODBC would install just fine using the install.packages('RODBC') command in R. However, after I installed unixODBC 2.3.0 from source, the RODBC install would fail because of a dependency issue, and I had to install RODBC from source using the guide posted here. Is it possible I failed to configure ODBC properly to begin with and that's why I'm having troubles now?
Answer
Wow, I thought I was the only person working on this kind of stuff. I had to solve the same problem and found the best solution was to use rjdbc. This is much easier to configure as ODBC on linux or osx was very spotty. RJDBC performance is great as it uses the native ms sql server jar to execute the query. Here is an example right out of one of our scripts. You just need to download the sqljdbc4.jar from microsoft and then install.package("RJDBC") into your environment. I realized you are trying to get ODBC to work and I did get it to work on osx but I gave up due to time for linux.
library(RJDBC);
drv <- JDBC('com.microsoft.sqlserver.jdbc.SQLServerDriver', '/usr/local/project/dataproviders/jdbc/sqljdbc4.jar', identifier.quote="'");
ch <- dbConnect(drv, 'jdbc:sqlserver://the.server.address.net;databaseName=DataWarehouse', 'USERNAME', 'PASSWORD');
allsupp_allprod_allproc <- dbGetQuery(ch, paste("SELECT [Date], Sum([Fail]) as Numerator, Sum([Pass] + [Fail]) as Denominator,'' as Annotation,'True' as 'Use for CL' FROM [PSU_YieldData] Group by [Date] Order by [Date]"));