Trying to connect to an ODBC server using RODBC in ubuntu

odbc, r, sql, sql-server

Solution

Here at my job, we use Centos 5.8. When I need to connect to our MS-SQL servers, I use FreeTDS drivers. I talk a bit more about it here: https://stackoverflow.com/a/10196098/1332389, including the packages and dependencies that I installed.

A sample connection string might look like this:

data_odbc <- odbcDriverConnect(connection="Driver=FreeTDS;
                                           Server=dataserver1\\instancename(default: master);
                                           Port=1433;        
                                           Database=database_01;
                                           Uid=data_mgmt;
                                           Pwd=placeholder")

We configured our odbcinst.ini file (in /etc/) to say:

# FreeTDS Drivers
# Manual setup, used for MS SQL
[FreeTDS]
Description     = FreeTDS for MSSQL
# 32 bit
Driver          = /usr/lib/libtdsodbc.so
Setup           = /usr/lib/libtdsS.so
# 64 bit
Driver64        = /usr/lib64/libtdsodbc.so
Setup64         = /usr/lib64/libtdsS.so
FileUsage = 1

I've had no issues since getting it set up. Hope this helps - I can try and answer if you have more questions.

Problem

I am getting the error: ``` [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found 2: In odbcDriverConnect("driver={SQL Server};server=*******;database=****;trusted_connection=true") : ODBC connection failed ``` Can anyone provide detailed instructions on installing the driver for ODBC in order for it to work with RODBC? Note: I am trying to connect to an MSSQL Server

Original source