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…

Accessing MS SQL Server from command line in Mac OS X and Linux/Unix

Unfortunately in my current line of work, I’ve been pulling data from MS SQL Server. My current workflow involves remotely logging into a windows machine, and from there, using the sql server software (MS SQL Server Management Studio) to issue queries and save data. This especially sucked in that I couldn’t write in the query file where I want my output results to go. I had to manually right-click the results, copy, and paste into a txt file.

Recently I started using Oracle’s SQLDeveloper with the 3rd party plugin jTDS to skip the remote login. SQLDeveloper allowed me to log directly into the SQL Server. This did not work well in that copying the results caused issues (memory?). Also, Query Analyzer was only available if the DB was Oracle’s. The added benefit was not great.

I finally found FreeTDS by googling “linux ms sql server command line.” To install it on Mac OS X, I needed to install it via Macports (sudo port install freetds). I had issues with it initially because it could not connect with the SQL Server of interest. The problem was that I needed to specify the correct TDS version; in my case, it was version 4.2 (see this linked from this thread). You can connect using two ways:

 <pre class="src src-sh"><span style="color: #eedd82;">TDSVER</span>=4.7 tsql -H myserverIP -p 1433 -U myusername -P mypassword

or

 <pre class="src src-sh">tsql -S myserverinlist -U myusername -P mypassword

with the following in “~/.freetds.conf”

 <pre class="src src-sh">[adrc]

host = myserverIP port = 1433 tds version = 4.2

See this for some examples. If you have connection issues, make sure you could telnet to the server using the right port first. See this. More information on the freetds.conf file can be found here.

For a query, remember to put go at the end of the query for it to execute (credit).

Be careful when routing input and output (“<” and “>”) as other messages from the tsql command will be present. Will post a solution when I find out.

Update 4/16/2010 – Batch output results from a query

I found out FreeTDS comes with the commands freebcp (bcp emulation/replication) and bsqldb. To get batch results, issue

bsqldb -U username -P password -S server.from.TDS.list -h -t '\t' -i query.sql -o query.out
## in 2nd line, has "-" characters (separate headers and data). to remove them:
bsqldb -U username -P password -S server.from.TDS.list -h -t '\t' -i query.sql | sed -n '2!p' > query.out

## or use freebcp
-- freebcp "$(cat query.sql)" queryout query.out -S server.from.TDS.list -U username -P password -c
## for freebcp, do not use "GO" in query

The above freebcp method does not work for me with the error Cannot bcp with TDSVER < 5.0.

Credit for for bsqldb goes to here. Removing the 2nd line is from here. This showed me how to get the correct freebcp command line syntax.

Next up is getting odbc (unixODBC to be exact) working!