Accessing MS SQL Server from Mac OS X (or Linux): FreeTDS, unixODBC, RODBC, R

This post is a followup to a recent post I wrote. The issue at hand is that data are stored on a SQL server (Microsoft Server 2005 or version 9 of MS SQL Server to be exact) and that I would like to send queries from a command line interface, either from Mac OS X or a Linux system. The data queried from the server will most often be analyzed in R.

I will outline how to get all of this set up on a Mac OS X which should be similar to a Linux platform.

NOTE: Do not use the Macports version of the following packages – they are NOT needed and can be compiled from source without a problem.

Compile unixODBC (driver manager for different SQL servers)

First, Mac OS X comes with iODBC installed by default. However, FreeTDS is only tested on unixODBC so I’ll use this instead. I didn’t get any luck with iODBC but I didn’t give it nearly as much time as I did to get unixODBC working.

Install like usual: download, extract, ./configure, make, sudo make install. I used the default install directory /usr/local.

Compile FreeTDS (driver to communicate with MS SQL Server and Sybase databases)

First, I recommend getting the development version since all the recent updates are included. I had issues with locale Mac OS X and TDSVER=8.0 (anything higher than version 4.2 actually) specification using FreeTDS v0.82. To do so:

cvs -z3 -d:pserver:anonymous@freetds.cvs.sourceforge.net:/cvsroot/freetds checkout -P freetds
cd freetds
./autogen.sh ## generate the configure and make files
./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
make
sudo make install

The configure line is taken from here. From that same documentation, we DON’T have to register the driver with the manager or add in the data source name for system wide access. I just dealt with local (home directory) files.

Recall from the previous post that I had issues with locale and TDSVER on Mac OS X. I emailed the mailing list and it was partially fixed (tsql and freebcp doesn’t work but bsqldb and isql works) in the development version of FreeTDS for TDSVER=8.0. My ~/.freetds.conf file:

[adrc]
host = IP.ADDRESS
port = 1433
tds version = 8.0
client charset = UTF-8 ##needed on MAC OS X

With this, I get query outputs in the command line:

$ bsqldb -S adrc -U USERNAME -P PASSWORD -h -t 't' -i katy.uds.sql | sed -n '2!p' > katy.uds.txt

To use isql (unixODBC), I have the following in my ~/.odbc.ini:

[adrcDSN]
Driver=/usr/local/lib/libtdsodbc.so
##Description=adrc
Trace=No
##servername=adrc
Server=IP.ADDRESS
##Address=IP.ADDRESS
Port=1433
TDS_Version=8.0
Database=IBADataDictionary
## can have above database or not

Now isql adrcDSN USERNAME PASSWORD works and looks similar to this.

Note, throughout my process of debugging, I usually precede the commands osql, isql, and tsql with environment variables such as `TDSDUMP=stderr TDSVER=8.0 isql …’ to see what’s going on.

RODBC – accessing the databases in R

The installed RODBC on my R in Mac OS X uses iODBC by default. I compiled from source to get it to work with unixODBC:

R CMD INSTALL -l ~/Rlib RODBC_1.3-1.tar.gz
## in R
library(RODBC, lib.loc="Rlib")
ch <- odbcConnect("adrcDSN", "USERNAME", "PASSWORD")

I’ve outlined how to access MS SQL Server from the command line and in R, both of what I want. Only thing missing is getting output in emacs…

About Vinh Nguyen

Statistician

13 comments

  1. Pingback: mssqlserver
  2. Hey Super Nerdy Cool,

    I’ve been working through this tutorial to connect to a MS SQL database and I’m still running into some issues.

    I was able to successfully install and compile the UnixODBC.

    When I tried to build the FreeTDS from source, I ran into some issues on the line: 3 ./autogen.sh ## generate the configure and make files

    The autogen failed from source so I tried doing it with MacPorts but that wasn’t much help either.

    with macports, I downloaded the prebuilt binary of freeTDS Upon build & install I couldn’t connect to the server due to incorrect file paths to freeTDS config files on the local machine

    Any help would be greatly appreciated.

    -Alex

  3. @Alex First, I left Mac OS X because of issues like this. I wouldn’t use FreeTDS from MacPorts as it is very old and not working (as with many packages in there). I think autogen isn’t working for you because you are missing some packages. You can try to post the message here and I can try to help find out which package you are missing, or you can google those message (or try to find out which command is missing) and install those missing packages.

  4. Hi,

    For me, make is failing:

    /bin/sh ../../libtool –tag=CC –mode=link gcc -D_THREAD_SAFE -g -O2 -Wdeclaration-after-statement -o tdspool config.o main.o member.o user.o util.o ../server/libtdssrv.la -liconv libtool: link: gcc -D_THREAD_SAFE -g -O2 -Wdeclaration-after-statement -o tdspool config.o main.o member.o user.o util.o ../server/.libs/libtdssrv.a -liconv make[2]: Nothing to be done for `all-am’. Making all in doc ./txt2man -I ‘FreeTDS User Guide’ -v ‘FreeTDS Utilities’ -t FreeTDS -r 0.83.dev.20110120 ./freebcp.txt >.freebcp.1 ./txt2man: line 167: gawk: command not found if test ! -s .freebcp.1; then echo ‘unable to generate freebcp.1′; fi unable to generate freebcp.1 test -s .freebcp.1 make[1]: *** [freebcp.1] Error 1 make: *** [all-recursive] Error 1 $

  5. You sir, are awesome. I’m a total noob to sql and was still able to get freetds up and running on my Mac so I could practice basic level SELECT style queries directly from this platform to the MS SQL running in a Fusion VM on it.

  6. Thanks for this awesome tutorial. I’m using Ubuntu, but it works just the same. After much hunting around and trial and error with information from message boards and other installation guides, your short tutorial worked the first time!

  7. I am ruuning following command on mac terminal.

    sudo port install freetds +universal

    i am not able to install successfully freetds with +universal flag.

    Please guide me..

  8. when I compiled the freetds , i just can’t generate the file : libtdsodbc.so
    what’s the problem with it?

  9. Hmm is anyone else having problems with the pictures on this blog loading? I’m trying to determine if its a problem on my end or if it’s the blog. Any suggestions would be greatly appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>