faulty motherboard

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

Comments are closed.