Free TDS: Execute Query & Send Result

Tabular Data Stream (TDS) is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server. For more info see wikipedia at URL http://en.wikipedia.org/wiki/Tabular_Data_Stream

FreeTDS is the open source implementation of the TDS protocol described briefly in the previous paragraph.
Often you must provide a query result in CSV or other text format for integration with other systems or simply to import the data to an Excel spreadsheet for pivot tables.

Obviously you can do this using the tools offered by SQL Server, but must have administrative rights.
In this short article I would like to illustrate how to run a query on SQL Server and send the results via email. I assume that on your Unix / Linux Box is up and running FreeTDS software, freely downloadable from the site http://www.freetds.org/.

Thanks to the simplicity of the Unix / Linux, you can compose complex commands from the composition of simpler commands. The command shown below, run the query contained within the sql file and then send an email with attached the query result.

tsql -H dbsql.lab.local -p 1433 -U www-1 -P ***** -D www-instance-1 -o fvq  < /tmp/sql_to_execute.sql > /tmp/out_sql_to_execute.txt && gzip /tmp/out_sql_to_execute.txt && mail -a /tmp/out_sql_to_execute.txt.gz -s "Email Object" antonio.musarra@gmail.com < /tmp/email_text.txt && rm /tmp/out_sql_to_execute.txt.gz

Tsql command (which is part of FreeTDS) sends the query to the SQL server specified by option-H, the result of the query is stored on the file. The output file is compressed by the command gz and finally sent as an attachment by email using the mail command. The command shown above could be useful in case you need to send data (text format) from your database application weekly or daily. Here is a simple shell script that could be put into crontab.

#!/bin/bash

FREETDS=/usr/local/freetds/etc
TDSVER=8.0
LC_CTYPE=en_US.UTF-8

export FREETDS
export TDSVER
export LC_CTYPE


DB_HOSTNAME="dbsql.lab.local"
DB_PORT=1433
DB_USER=www-1
DB_PWD=*****
DB_NAME=www-instance-1


SQL_FILE_NAME="/tmp/sql_to_execute.sql"
SQL_FILE_OUT="/tmp/sql_to_execute-date +%F.txt"
EMAIL_TO="antonio.musarra@gmail.com"
EMAIL_CC="hd@example.com"
EMAIL_TEXT="/tmp/email_text.txt"


/usr/local/freetds/bin/tsql -H $DB_HOSTNAME -p $DB_PORT -U $DB_USER -P $DB_PWD -D $DB_NAME -o fvq  < $SQL_FILE_NAME > $SQL_FILE_OUT && gzip $SQL_FILE_OUT && mail -a $SQL_FILE_OUT.gz -c $EMAIL_CC -s "Check Consistenze GLS" $EMAIL_TO < $EMAIL_TEXT && rm $SQL_FILE_OUT.gz

Hoping to be helpful to someone.