tsql – Query MSSQL from the command line

Problem:
Run the same query on a MSSQL database several times.

Solution:
tsql

Background.
I had to get data for various months from a table on MSSQL. Rather than sit down in front of the computer and manually change each value and re-run the query using SQL Query Tool(?) I opted to use the command line. So I moved to a linux box that could:
1. Access the Windows box that had MSSQL server installed.
2. Had tsql installed.

First I wrote the query.
$cat query

use cars 
go
SELECT LEFT(RecordTime, 11) AS Date, COUNT(CarType) FROM sales WHERE datepart(yyyy, RecordTime) = '2010' AND datepart(mm, RecordTime) = '$month' GROUP BY LEFT(RecordTime, 11) ORDER BY LEFT(RecordTime, 11)
go

$month is the variable that is going to change

On the linux box.
Check that I could connect remotely

shell>tsql -S [server-ip-address] -U [username] -P [password]
locale is “C”
locale charset is “ANSI_X3.4-1968”
1> exit
shell>

Once I knew I could connect I just needed to run the queries. So on the command line I did

shell>for month in 01 05 10 11 12; do sed “s/\$month/$month/” query | tsql -S [server-ip-address] -U [username] -P [password] > results_$month.csv; done

Thats it.

MSSQL converting timestamps

I needed to convert timestamps from the default Dec 01 2009 10:55PM to 2009-12-10.

Its a simple as

SELECT top 5 convert(varchar(11),messagetimestamp,121) FROM data

More formats can be found here

http://www.w3schools.com/SQL/func_convert.asp