8

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

Nick Trileski
  • 151
  • 1
  • 2
  • 8
  • 1
    @JDLong had a similar problem. I suggested he try [RJDBC](http://cran.r-project.org/package=RJDBC) instead and I think that worked... – Joshua Ulrich Jun 18 '12 at 14:35
  • Im working on RJDBC its just i am having a problem with rJava-- it works in my R console (connects to the server and everything) except when I try to run it through rApache it crashes as i posted in another thread. – Nick Trileski Jun 18 '12 at 15:06

2 Answers2

9

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.

caot
  • 3,066
  • 35
  • 37
TARehman
  • 6,659
  • 3
  • 33
  • 60
  • Thank you, you sent me to the right direction. I had my connection string with Driver={SQL Server} from my Windows machine. Even after installing the SQL Server drivers on my linux machine it didn't work. I just had to replace the driver name with the one from my /etc/odbcinst.ini (which is {ODBC Driver 17 for SQL Server}). – NDUF May 23 '19 at 03:19
4

I had similar problem using remote R session on Linux, with RJServer, connecting to it from StatET under Eclipse. The error looked like this:

[RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib '/usr/local/easysoft/sqlserver/lib/libessqlsrv.so' : file not found

The difficulty of solving this problem was that the error message is misleading: the file shown in the message is perfectly visible (I could test it). The "secret" is that "file not found" refers to another dependent library.

Bottom line: I added few elements to the LD_LIBRARY_PATH used in the remote session, in my case it was: /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib

To figure out what you need to add, you should see what is missing. Use "ldd" command on the library in question.

Leonid
  • 41
  • 1