3

My question is related to

Trying to connect to an ODBC server using RODBC in ubuntu

and

How to specify include and lib directories when locally installing RODBC?

but I could not find suitable answers to my case.

I want to connect to a SQL Server database on a remote server using RODBC.

I have installed unixodbc and freetds, and can connect in the terminal with T-SQL, so the connection exists.

But when trying to connect in R with (all sensitive info have been replaced by ***):

odbcConnect(dsn="TESTSQL", uid=***, pwd=***)

I get:

Warning messages:
1: In RODBC::odbcDriverConnect("DSN=TESTSQL;UID=***;PWD=***") : [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open         lib '/usr/local/Cellar/freetds/0.95.18/lib/libtdsodbc.so' : file not found
2: In RODBC::odbcDriverConnect("DSN=TESTSQL;UID=***;PWD=***") :
  ODBC connection failed

The odbc.ini file being:

[ODBC Data Sources]
TESTSQL     = Test database

[TESTSQL]
Driver      = MSSQL
Servername  = ***.**.**.**
Port        = **
Database    = ****
TDS_Version = 8.0

I had installed the latest version of freetds, that is 1.00.27, I am hence surprised that this library libtdsodbc.so is missing.

Is that normal? Would you recommend to install the version 0.95.18 or rather stay with 1.00.27 and look for that missing library?

Community
  • 1
  • 1
Xavier Prudent
  • 1,570
  • 3
  • 25
  • 54
  • If you did install the library, where do you find `libtdsodbc.so`? My guess is that it is not under `/usr/local/Cellar/...`, based on the error message. (If you don't know hot to find it, try either `local libtdsodbc.so` or, if that fails, `grep libtdsodbc.so /var/lib/dpkg/info/*list` or if desparate `find / -type f -iname libtdsodbc.so`.) – r2evans Apr 19 '17 at 21:46
  • might want to hide your username and password from the error – Ian Wesley Apr 19 '17 at 22:39
  • I had looked for libtdosbc.so, without success, with grep and find: grep libtdsodbc.so /var/lib/dpkg/info/*list grep: /var/lib/dpkg/info/*list: No such file or directory I had installed it with brew install --with-tdsver=8.0 --with-msdblib --with-unixodbc=/usr/local/Cellar/unixodbc/2.3.4/ freetds Ian: infos have been removed from the message, thx – Xavier Prudent Apr 20 '17 at 03:21

1 Answers1

1

I had to remove freetds:

brew remove freetds

then resintalling it, specifying --with-unixodbc to have the libtsdodbc.so created:

brew install freetds --with-unixodbc

In the odbc.ini, I had then to take care not to confuse "Server" and "Servername", and link the driver to the libtdsodbc.so, so that my odbc.ini looks like:

[ODBC Data Sources]
TESTSQL     = Test database

[TESTSQL]
Driver      = /usr/local/lib/libtdsodbc.so
Server      = ***.**.**.**
Port        = **
Database    = ****
TDS_Version = 8.0

and connected using the RODBC package

ch1 <- odbcConnect(dsn="TESTSQL", uid=***, pwd=***)
> ch1
RODBC Connection 5
Details:
  case=nochange
  DSN=TESTSQL
  UID=****
  PWD=******

it works!

Further detailed informations from this page

http://eriqande.github.io/2014/12/19/setting-up-rodbc.html

Xavier Prudent
  • 1,570
  • 3
  • 25
  • 54