We haven’t talked tech in a while on our blog but since a good percentage of our readers consists of programmers and database administrators, we feel the need to sneak in an article or two every once in a while. So if this one doesn’t interest you, don’t sweat it; more cool, mainstream articles are coming.
For our techie friends, this information is a subset of a great article from Steve Moore on SQL Server Central. His source article is here in full.
Email Formatted HTML Table with T-SQL
One of the most common tasks for me is to send data from a query as the body of an e-mail. You can do this in SSIS by outputting a query to a flat file, but there’s no easy way to format the content. You have the option of using XML and XSLT transformations, but that is a bit onerous for simple use cases.
This article is about how to produce a table in an e-mail using just SQL. The only real complexity is formatting alternate rows, which was my goal. I wanted it to look like a .NET DataGrid.
The basic approach involves three steps:
- Create a query that pulls together the data as HTML.
- Use bcp and xp_cmdshell to write the SQL to a file.
- Email the file using Blat, a free SMTP client (www.blat.net)
One of the problems I ran into is the use of a <script> tag in the email, so there I had to abandon CSS styling and instead do inline styling on every row.
In order to find the odd/even rows, you need the ROW_NUMBER() function in a CTE, then use a CASE statement, and the modulo operator.
Once you have the SQL with HTML formatting, you want to use that within another stored procedure to write the result to a file.
Now you should be able to open the file “C:MyProjectSqlHtml.html” in a browser and it should look similar to a .NET DataGrid. Finally, send the file as the body of an e-mail.
Thanks to Steve for the great explanation. For those interested, he shows full explanations of the stored procedures in his article on SQL Server Central here.
image credit: javaprogrammingforums.com