Archives
Our Thought in One Place

  • 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