Query mssql from bash

written by billy September 28, 2012

Tabular Data Stream (TDS) is an application level protocol that describes the transmission of data between two computers. TDS defines the types of messages that can be sent as well as the order in which the messages can be sent in.The TDS protocol was designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine. Microsoft introduced TDS is an application layer protocol, used to transfer data between a database server and a client.

So, how can you use this with MS SQL server? Enter FreeTDS. It implements TDS 4.2, 5.0, 7.0 and 8.0, and can communicate with any Sybase or Microsoft SQL Server. You can see a full example of usage at the very end of this post

Most Linux distributions include freetds as a binary package. Installation on Ubuntu looks like this

However, if you need to compile yourself it is quite straight forward. Follow the standard ./configure && sudo make && sudo make install.

Once installed you need to configure the freetds.conf. The location may vary based on binary vs. self compiled installation but most often you will find it as /etc/freetds.conf or /usr/local/etc/freetds.conf. It is this that contains the references to the MS SQL server you wish to connect to. As seen in the example below only a few lines need to be added for each SQL server.

You should modify the freetds.conf file to include the SQL server you intend to connect to. You can copy and paste the “typical Microsoft server” entry in freetds.conf changing the friendly name (in []) and the host name to match your MSSQL eserver. Once your freetds.conf file has been configured it is time to test it out. FreeTDS comes with a few nifty little programs to query a database in bash like tsql and bdsqldb. I personally use bsqldb a lot and will take a quick look at it here. On Ubuntu you will need to install the freetds-bin package in order to get these utilities.

Alrighty, now that we have everything in place for bash, let’s connect to a SQL server. You can get an idea of how it works using the -h argument.

Using these options you can construct a function for your bash scripts which will connect to a SQL database, run a query, and provide the results back to you. Let’s take a look at a full script.

As you can see from this simple introduction to installing and configuring FreeTDS it is a very powerful utility when you need to connect from Linux or Unix servers to a MS SQL Server (or Sybase server). Once configured, FreeTDS can be used in perl scripts, command line, shell scripts, PHP, and unixODBC.

 

Leave a Comment