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 = 'my_account@email.com; my_account2@email.com';
$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:
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='jeffrey_yao@hotmail.com' , @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:
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.
