Connect to a SQL Server database through RODBC

Question

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?

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
– r2evans, Commented Apr 19, 2017 at 21:46
might want to hide your username and password from the error — Ian Wesley
– Ian Wesley, Commented Apr 19, 2017 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
– Xavier Prudent, Commented Apr 20, 2017 at 3:21

Xavier Prudent · Accepted Answer · 2017-04-20 14:41:37Z

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

answered Apr 20, 2017 at 14:41

Xavier Prudent

1,7924 gold badges31 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect to a SQL Server database through RODBC

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related