Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Generate HTML Formatted Emails from SQL Server


By:   |   Read Comments (10)   |   Related Tips: More > 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 [email protected]"
<!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.



Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 @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

@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

 @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 09, 2018 - 12:34:36 PM - jeff_yao Back To Top

@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 09, 2018 - 9:33:59 AM - Costin Back To Top

 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.


Friday, June 09, 2017 - 12:13:47 PM - Wagner Back To Top

†Hi†all...

Im facing a problem on generating a mail using SQL with output a HTML table on mail body...†
My query returns something like 200 row, not too big, but the mail is more bigger than 8mb...†

How can I generate this HTML resources decrease this size table, cause I need run big queries result?

------------------------------------

My Code (simple way)

------------------------------------

DECLARE @tableHTML †NVARCHAR(MAX) ;

SET @tableHTML =

N''+

† † N'

Work Order Report

' +

† † N'

' +

† † N'

' +

† † CAST ( ( SELECT td = ACAWADVO_INSCRICAO+''+ACAWADVO_TIPO_INSCRICAO, †

td = ACAWADVO_NOME,'',

† † † † † † † † † † td = Isnull(Cast(ACAWADVO_ENDERECO+' '+ACAWADVO_BAIRRO+'-'+ACAWADVO_CIDADE+'/'+ACAWADVO_ESTADO +' †CEP:'+ACAWADVO_CEP as Nvarchar(250)),''),†

† † † † † † † † † † td = IsNull(ACAWADVO_FONE,'') +' - '+ IsNull(ACAWADVO_CELULAR,''), ''

† † † † † † † FROM BISA_CAW.DBO.ACAWADVO†

where ACAWADVO_SEXO = '2' and ACAWADVO_TIPO_INSCRICAO = 'D'

† † † † † † † FOR XML PATH('tr'), TYPE†

† † ) AS NVARCHAR(MAX) ) +

† † N'

INSCRI«√O NOME ENDERE«O TELEFONE
'+

N'' ;

--print @tableHTML

EXEC msdb.dbo.sp_send_dbmail†

@recipients='',

† † @subject = 'SQL Errors Report',

† † @body = @tableHTML,

† † @body_format = 'HTML',

@profile_name = 'Mail'†


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

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

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

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

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.


Learn more about SQL Server tools