Friday, April 5, 2013

Access SQL Server from Linux

Let's face it.  Even since back before they started labeling the sql server releases as 2000 whatever, there has been a need to access sql server from other operating systems.  I'm sure java would be a slick way to go, but for an  administrator used to living at a bash prompt, that just won't cut it.

Thankfully, as part of some consulting work, I stumbled upon FreeTDS.  It's pretty slick.
1.  Download FreeTDS from

2.  Install FreeTDS
 - copy the compressed file to the target server.
 - uncompress the software bundle.
 - cd into the FreeTDS source.
 - ./configure
 - make
 - make install
 - the default is to install to /usr/local

3.  Configure FreeTDS
 - cd /usr/local/etc
 - vi freetds.conf
 - add in a line for the target server
 # A typical Microsoft server
     host =
     port = 1433
     tds version = 7.0

4.  Query your server
 tsql -S <your name from the freetds.conf file> -U <username> -P <password>
 If my user was admin and my password was friday, server name EvolveDB
 tsql -S EvolveDB -U admin -P friday

5.  Use tsql in batch mode
 tsql is very limited.  Simply use redirects to either run scripts or capture output.
 For example, using the above connection, and running a script named mike.sql and capturing that to mike.out
 tsql -S EvolveDB -U admin -P friday < mike.sql > mike.out

I've found that tsql does some funky things with the output.  For example, you can pipe it directly to awk, but if you toss sed in there as well to just remove spaces, it blitzes the tabs as well.  I've had the best luck sticking a ~ in as a delimiter, then using that with awk to parse the columns back out.

No comments:

Post a Comment