Connecting to Microsoft SQL Server on ODBC using R in Ubuntu

Question

I was able to get it completely working on Windows 7, but I have to move it to a Linux server. I'm running Ubuntu 16.04.1 LTS 64-bit in a VM right now for testing before I replicate the process on the server.

I'm pretty sure the issue is somewhere in my FreeTDS or DSN setup.
Should I stop wasting my time and just use Python? Or will I have this same issue with ODBC and unixODBC?

Full R script:

library(RODBC)

#saving from a .csv to dataframe df
df <- read.csv("./Documents/test.csv")

#creating connection to db
conn <- odbcDriverConnect('myDSN')

#writing
sqlSave(conn, df, tablename = 'dbo.test0', append = F, rownames = F, verbose = TRUE, safer = true, fast = F)

R Console

> conn <- odbcDriverConnect('myDSN')

Returns error message:

Warning messages:
1: In odbcDriverConnect("myDSN") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("myDSN") : ODBC connection failed

Here's my ODBC and FreeTDS config:

/etc/odbc.ini

[myDSN]
APP = unixodbc
Description     = master on DBNAME
Driver          = TDSdriver
Server          = SERVNAME
Database        = DBNAME
Port            = 1433
Username = UNAME
Password = PW
#Trace           = No
#TraceFile       = /var/log/freetds/freetds--odbc.log

/etc/odbcinst.ini

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so


[myDSN0]
APP = unixodbc
Description     = user on DBNAME
Driver          = TDSdriver
Server          = SERVNAME
Database        = DBNAME
Port            = 1433
Username = UNAME
Password = PW
#Trace           = No
#TraceFile       = /var/log/freetds/freetds--odbc.log

/freetds/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

[DSN0]
        host = IPADDRESS 
        port = 1433
        tds version = 7.2
        client charset = UTF-8

I don't think this is R specific. It's been awhile since I've connected to SQL Server from Linux, but remember having a hard time with Python as well. — duffn
– duffn, Commented Dec 29, 2016 at 22:09
Why don't you use Microsoft's ODBC Linux driver for SQL Server? Also note that your ini files use a lot of different names - myDSN, myDSN0 and DSN0 — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 30, 2016 at 17:17

blongworth · Accepted Answer · 2016-12-30 17:11:19Z

1

I have RODBC -> FreeTDS -> SQL Server working on linux Mint. It's been a while since I've configured it, but here are some (untested) suggestions:

There are some good install notes at: http://www.saltycrane.com/blog/2011/09/notes-sqlalchemy-w-pyodbc-freetds-ubuntu/

and of course RTFM: http://www.freetds.org/userguide/

Can you narrow down the problem by using isql mydsn or tsql -S mydsn from the command line? Pretty sure this will tell you that your problem is in the FreeTDS setup.

It looks like your odbcinst.ini has a few diffs from mine:

[TDSDriver]
Description = TDS driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

My freetds.conf is the same except tds version = 8.0 and the name of my server entry is the same as it's entry in odbc.ini.

This and the docs should hopefully get you there.

answered Dec 30, 2016 at 17:11

blongworth

4193 silver badges13 bronze badges

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

2 Comments

Panagiotis Kanavos Over a year ago

You don't need to go through FreeTDS when a provider-supplied ODBC driver is available.

blongworth Over a year ago

Has anyone tested FreeTDS against MS ODBC for Linux? I'd be interested to see which is faster.

Collectives™ on Stack Overflow

Connecting to Microsoft SQL Server on ODBC using R in Ubuntu

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related