Generate HTML Formatted Emails from SQL Server

By:   |   Comments (9)   |   Related: > Database Mail


Problem

I often need to send SQL Server query results in an email to various people. For example, alert emails about low disk free space or consistent high CPU usage, business related reports, etc. I want the results in a well-formatted tabular form. How can I do so?

Solution

SQL Server has a system stored procedure sp_send_dbmail, which can take multiple queries with its @query parameter, and then email the results. However such results do not have any decent formatting capabilities, thus the results are difficult to read, especially when the @query parameter has multiple queries and each query has a different number of columns in the result set. The good thing is that sp_send_dbmail system stored procedure has one flexible feature, i.e. it can send email in HTML format, and we know HTML itself has strong formatting capabilities. So the solution in this tip will be focused on how to compose a well-formatted HTML string that can wrap around the query results.

Solution Design Key Points

  • Create an HTML template with CSS. In CSS, we define HTML table properties, such as table caption, border, row appearance, background, font size, etc. In my below code, I define three ID elements for <TR> so in an HTML table, you will see rows are interlaced with different background colors and we will repeat that pattern every three rows. Of course, you can define as many ID elements for <TR> as you like.
  • Use SMO Database.ExecuteWithResuts method to execute one or multiple queries and get a DataSet object.
  • Look through each DataTable in the DataSet.Tables.
  • In each DataTable, loop through each DataRow and construct an HTML table row.
  • Complete the whole HTML string, and pass the string to sp_send_dbmail's @body parameter and send the email.

This solution utilizes PowerShell and CSS to generate a HTML string that can be sent to the @Body parameter of the sp_send_dbmail system stored procedure.


#for sqlserver 2008 or 2008R2, version=10.0.0.0, for sql 2012 version=11.0.0.0, for sql2014, version=12.0.0.0
add-type -AssemblyName "microsoft.sqlserver.smo, version=11.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91"
<#
The following four variables, i.e. $recipients, @subject, @sql_server, $dbname need to be modified according to your own environment
$TableCaption is an array that will host the table captions for each table in the email. 
$qry parameter needs to be modified as well, it can be a stored procedure, an complex select statement etc that returns your expected 
query results.
#>

$recipients = '[email protected]; [email protected]'; 
$subject ='Daily Report';
$sql_server= $env:COMPUTERNAME; #change to your own server, default is the current server 
$dbname ='AdventureWorks2012'; #modify accordingly
[string[]]$TableCaption="Regional Sales", "Product Catalog"; 

[string]$qry = @"
select top 10 RegionName=Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear 
from sales.SalesTerritory;

select top 10 Name as ProductName, PN=ProductNumber, color from Production.product;
"@;
$s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sql_server; #windows authentication
$db = $s.databases.Item($dbname);
$t =$db.ExecuteWithResults($qry);

#you can provide multiple id sections, in the format of #id<N>, where N is a numeric in sequence.
[string]$html =@"
<!DOCTYPE html>
<html>

<head>
<style>
#id0 {	background-color: rgb(200, 240, 200);	}

#id1 {	background-color: rgb(240, 200, 200);	}

#id2 {	background-color: rgb(200, 200, 240);	}

table, tr, th, td {
	border:1px solid black;
	border-collapse:collapse;
}
Caption {font-weight:bold; background-color:yellow;}

</style>

<title>DBA Report</title>

</head>

<body>
TO-BE-REPLACED 
</body>
</html>

"@;

[string]$body='';
[int]$tbl_seq=0;
foreach ($tbl in $t.tables)
{
    [int]$row_num =0;

    [int]$i=0

    if ($TableCaption[$tbl_seq] -ne $null)
    { $body += "<table><caption>$($TableCaption[$tbl_seq])</caption> `r`n" + "<tr>                `r`n"; }
    else #no table caption, so just ignore
    { $body += "<table><tr>                `r`n"; }
    foreach ($col in $tbl.Columns)
    { 
        $body += "<th>$($col.ColumnName)</th> `r`n";
        $i += 1;
    }
    $body += "</tr>";

    foreach ($r in $tbl.Rows)
    {   
        $body +="<tr id=`"id" + "$($row_num%3)`">"; # $ow_num%3, we use divisor 3 because we have 3 id sections in the sytle sheet
        for ($j=0; $j -lt $i; $j++)
        {
            if ($r.item($j) -is 'DBNull')
            { $body += '<td></td>'; }
            else
            { $body += "<td>$($r.item($j))</td>"};
        }
        $body +="</tr> `r`n";
    
        $row_num += 1;
    }#loop each row $r

    $body += "</table> `r`n <p> </p> `r`n";

    $tbl_seq +=1;

} #loop via each datatable in the dataset;
$body = $body -replace "'", "''";
$body=$HTML -replace 'TO-BE-REPLACED', $body;

$qry="exec msdb.dbo.sp_send_dbmail @recipients='$($recipients)', @subject='$($subject)', @body_format='html', @body='$($body)'";
$db.ExecuteNonQuery($qry);

Since, the script will use sp_send_dbmail, we assume that the database mail configuration is set up already on [$sql_server] instance. You can copy and paste the code into a PowerShell ISE window, make the necessary change to the first few variables and then run the code.

SQL Server Email Results Using HTML Formatting 

You will receive an email like the following that returns result sets from the two queries that were run:

Multiple Query Reults

SQL Server Email Results Without Formatting

On the other hand, if we use the native sp_send_dbmail system stored procedure to send the query results, the code would be as follows: 

exec msdb.dbo.sp_send_dbmail @recipients='[email protected]'
, @subject='Daily Report'
, @query='select top 10 RegionName=Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear 
from sales.SalesTerritory;
select top 10 Name as ProductName, PN=ProductNumber, color from Production.product;'
, @execute_query_database = 'AdventureWorks2012'
GO

We will receive the following email that I consider an eye-sore:

Native Multiple Query Reults

Summary

In this tip, we use PowerShell and .Net Systme.Data objects, plus HTML and CSS, to send emails in tabular format. Compared with a pure T-SQL solution, it does not need any hard-coding for column names, i.e. if you have a query such as "select * from dbo.MyTable", you can get the tabular format email without changing anything. This is not possible (in some cases, such as a stored procedure using Dynamic SQL to return results) or at least very hard to achieve with a pure T-SQL solution, especially when handling multiple queries.

Next Steps

You may enhance the script to use more sophisticated visual effects.

Refer to the following similar tips which use a pure T-SQL solution to do similar work and also how to setup SQL Server Database Mail.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, January 12, 2018 - 3:08:25 AM - Costin Back To Top (74947)

 @jeff_yao i ran the script directly from the server to avoid any limitation. This is working. Thank you very much!

Wednesday, January 10, 2018 - 5:30:51 PM - jeff_yao Back To Top (74940)

@Costin, since you can run the generated c:\tem\a.sql inside a SSMS window, it means your db mail is set up correctly. So now I suspect there is some permission issue when you run the last line, i.e ExecuteNonQuery, I'd suggest you to try to run the PowerShell script as sysadmin (for your sql server instance).

Also, what is the version of your sql server ?

I have run the PS script in mutliple versions (SQL Server 2012/14/16) and there is no issue at all as long as the db mail is set up.

Wednesday, January 10, 2018 - 8:51:46 AM - Costin Back To Top (74935)

 @jeff_yao thank you for your quick reply

i executed the script that you gave me and from sql the mail is sent succesfully

 The issue appear on this function ExecuteNonQuery. As far as i know this should not receive 2 parameters but in this specific case it is not possible.

Please help

Tuesday, January 9, 2018 - 12:34:36 PM - jeff_yao Back To Top (74928)

@Costin, it is really hard to pinpoint your issue without more info. I'll try my best and see whether the following will help you.

Your error seems to point to the last line of code. What you can do is comment out the last line (i.e. sending the emai), and instead put the following line

$qry | out-file -FilePath c:\temp\a.sql -Force;

 

Then use open c:\temp\a.sql inside your SSMS, and run it and see whether you can run the script, which is just a db mail sending t-sql.

 

Tuesday, January 9, 2018 - 9:33:59 AM - Costin Back To Top (74925)

 Hello,

I tried to run this script but it gives me the following error:

Exception calling "ExecuteNonQuery" with "1" argument(s

At D:\Powershell\myfirstscript.ps1:102 char:1

 

Please help.

Thursday, September 24, 2015 - 12:13:00 PM - jeff_yao Back To Top (38749)

Thanks @Scott, I always think that forum discussion following an artilce is really valuable not only to readers but also to the author. So thanks again for your comment. 

Thursday, September 24, 2015 - 10:44:07 AM - Scott Back To Top (38747)

You are correct that I overlooked the dynamic query aspect of your post, probably because I was so anxious to add your CSS row highlighting to the code I had.  I don't think I read as far as your Summary paragraph before I was playing with the code.  Plus I don't think I have ever had a need to send pretty emails of random queries, only fixed queries with manually-defined headings and probably custom formatting on individual columns.

But if I did have to rewrite the code often for various queries, I would also switch to PowerShell or C# fairly quickly.

Wednesday, September 23, 2015 - 6:48:00 PM - jeff_yao Back To Top (38740)

Thanks @Scott for your comments. But you may miss the core messages of this tip.

Yes, FOR XML can create an HTML-formatted table (which is one of the most common ways we can google out). However, this "FOR XML" way does not (easily) address the "dynamic column" output issue. Let's use an example,

if I want to send an HTML table report for "sp_who2 active", what do you do? I guess you may need to create a table to first hold the result from sp_who2, and then you hard-code the columns in the FOR XML query script.

But the PS approach in this tip does not need any manual work here, all you need to do is to modify two lines as the following:

 

[string[]]$TableCaption="Current Activity"; 

[string]$qry = @"
exec master.dbo.sp_who2 active
"@;
When you have complex queries (i.e. multiple joins with CTE etc) and some times you may need to change the colum names or column list, with "FOR XML" approach, it will be a big cost for maintenance. But with this PS approach, you really do not worry about anything, you just change the $qry and that is. It is extremely useful when you need to report the result from one or multiple stored procedures. 
This PS script can be easily converted to a PS function so it can be used in command line to do dynamic reporting as a command with different @qry parameter.
I hope this explanation will explicitly outline the advantanges of this tip over the "FOR XML" approach (which had been my way before, until I cannot tolerate the pain for the maintenance.)
 

Wednesday, September 23, 2015 - 12:15:33 PM - Scott Back To Top (38738)

How about generating those emails in T-SQL?  A FOR XML query can do a decent job of building an HTML-formatted table.

declare @body varchar(max);
SELECT @body = '<!DOCTYPE html><html><head><style>
#id0 { background-color: rgb(200, 240, 200); }
#id1 { background-color: rgb(240, 200, 200); }
#id2 { background-color: rgb(200, 200, 240); }
table, tr, th, td {
    border:1px solid black;
    border-collapse:collapse;
}
Caption {font-weight:bold; background-color:yellow;}
</style>
<title>DBA Report</title>
</head><body>
<table align="left" cellpadding="5" cols="8" frame="vsides" rules="rows" width="90%">
<caption align="top">HTML email example from sys.tables query</caption>
<thead>
      <tr> <th>Name</th> <th>ObjectID</th> <th>Type</th> <th>Created</th> <th>Modified</th> <th>System obj</th> <th>Ansi nulls</th> <th>Text-in-row</th> </tr>
</thead>
<tbody>' + (
    SELECT [@id] = 'id' + LTRIM(ROW_NUMBER() OVER (ORDER BY name) % 3),
         td = ISNULL(name,' '), '',
         td = ISNULL(LTRIM([object_id]),' '), '',
         td = ISNULL(type_desc,' '), '',
         td = ISNULL(CONVERT(CHAR(19), create_date, 120),' '), '',
         td = ISNULL(CONVERT(CHAR(19), modify_date, 120),' '), '',
         td = ISNULL(LTRIM(is_ms_shipped),' '), '',
         td = ISNULL(LTRIM(uses_ansi_nulls),' '), '',
         td = ISNULL(LTRIM(text_in_row_limit),' ')
    FROM sys.tables
    ORDER BY name
    FOR XML PATH('tr') )
    + '</tbody>
<tfoot>
      <tr> <th colspan="8" align="right">And that''s how to format email queries</th> </tr>
</tfoot>
</table></body></html>';

EXEC msdb.dbo.sp_send_dbmail
    @recipients = '[email protected]',
    @subject = 'HTML email test',
    @body = @body,
    @body_format = 'HTML'

This resulted from merging your CSS with an existing script I had, so possibly more of the custom attributes I used could be moved to the style section (HTML and CSS is not my strong suit).  I'll take credit for the XML query, everything else I copied from something I found on the internet.

Query notes: The anonymous empty string columns between the td columns are to prevent FOR XML PATH from merging them all into one td field.  ISNULL must be used with all nullable columns, any NULL value would be left out of the result and screw up the row.  LTRIM(...) is used as shorthand for CAST(... AS VARCHAR).  The query will not correct any special characters that get converted into &amp;, &lt;, &gt;, etc.














get free sql tips
agree to terms