RODBC MS SQL Access From Ubuntu Using FreeTDS

I'm trying to access MS SQL server hosted on my Windows box from an Ubuntu machine. I have a shiny app that access MSSQL that works fine on Windows. I want to host this with shiny

Solution 1:

I fixed this. TDS version was needed in odbc.ini file.

TDS_Version = 8.0

tsql reads version info from freeTDS.conf and worked. isql was failing with same error and it was also looking for this config in odbc.ini. So test with isql if you are configuring for R/Python.

Solution 2:

I don't know what is causing the error on your side. Hope this helps:

These are my settings for RODBC 1.3-10 to connect to MSSQL Server 2012. I am also on ubuntu and this works for me. I think I installed the RODBC package from the Ubuntu repos:

apt-cache policy r-cran-rodbc
r-cran-rodbc:
  Installed: 1.3-10-1
  Candidate: 1.3-10-1
  Version table:
 *** 1.3-10-1 0
        500 ftp://ftp.fu-berlin.de/linux/ubuntu/ trusty/universe amd64 Packages
        100 /var/lib/dpkg/statu

uname -a

Linux xxx 3.13.0-30-generic #54-Ubuntu SMP Mon Jun 9 22:45:01 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

odbc.ini

[mydsn]
APP = unixodbc
Description     = master on vmXX
Driver          = TDSdriver
Server          = vmXX
Database        = master
Port            = 1433
#Trace           = No
#TraceFile       = /var/log/freetds/freetds--odbc.log
TDS Version     = 7.2

freetds.conf

[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        #dump file = /tmp/freetds.log
        #debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512




[vmXX]
        host = 111.222.333.555
        port = 1433
        tds version = 7.2
        client charset = UTF-8

Output from

ps aux  | grep "/R"
knb      56969 ....
lsof -p 56969 | grep -i odbc

R       56969  knb  mem    REG     8,1    72408  8528592 /usr/lib/x86_64-linux-gnu/libodbcinst.so.1.0.0
R       56969  knb  mem    REG     8,1   400608  8521896 /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
R       56969  knb  mem    REG     8,1   419680  8525415 /usr/lib/x86_64-linux-gnu/libodbc.so.1.0.0
R       56969  knb  mem    REG     8,1    96845  8933205 /usr/local/lib/R/site-library/RODBC/libs/RODBC.so

Solution 3:

I had a similar error and realized R was just unable to read my user id correctly as it had a slash in it. I placed my credentials in a text file which solved the problem for me.

cred <- "/myPath/ODBC_cred.txt"

sql <- RODBC::odbcConnect("abc.xyz.com", uid=readLines(cred)[1], pwd=readLines(cred)[2])