Home / Productivity / Automating SQL reports with sqlcmd and sendemail

Automating SQL reports with sqlcmd and sendemail

The Windows platform certainly has its advantages, but one of the things I miss about working in a *nix environment is the power of the command line. There’s something satisfying about automating ridiculously complex tasks by editing one line in a text file on your system, then forgetting about it.

Case in point. I have a data set that we are building, and every morning I log in, open up SQL management studio, and execute a query to check on its progress, then email someone else with that report. The SQL server in question is on a hosted server, and doesn’t have reporting, business intelligence, and I don’ t even have admin access to the box (the shame!). On a linux box I’d just do a cron job to pipe the mysql query results to sendmail and be done with it, but how to do this on a Windows box?

There are a few options. Windows doesn’t ship with a build-in command line mailer, but there are some open source options. There is a version of the classic sendmail available for windows, but that involves some setup and editing of INI files, and I don’t have the patience for that. There’s also a delightful little program called “Sendemail.exe“, with no external requirements. Simply download the exe, add it to your PATH and you can send emails from the command line with ease. In my case, I can now take the input of that daily query, run it via sqlcmd (you should already have sqlcmd if you’re doing any SQL dev). and pipe it to sendemail.exe. You will need to send the “from” and “to” addresses, and your SMTP server:

sqlcmd -U your_ sql_user -P  your_sql_pass -S your_sql_server -d your_sql_db -Q your_sql_sproc | sendemail -f auto@home -t your_email_address -s your_smtp_server

There is one major flaw to sendemail.exe. Messages get truncated at around a page or two (32000 characters perhaps?), but for quick little queries it gets the job done.