Generating professional email notifications with SQL

Hi Folks,

It would be nice to have SQL send email notifications that are HTML based with HTML formatting


This is possible because of the following. HTML tables are made of Tags called TD and TR, in SQL we have the FOR XML statement and we can actually cheat the SQL server into thinking that it is creating a XML layout where in fact it is building a HTML table layout.

Below is a sample email sent by SQL Server


Notice the failure column, here we have different colours, if there are errors, it sets the background to Red.


SQL Code

Below is the SQL Procedure that does this.

USE [btwf_BizTalkWorkFlowSupport]
/****** Object:  StoredProcedure [MetaData].[mmit_sp_BizTalkDailyFailureReport]    Script Date: 09/21/2008 09:22:40 ******/
–CREATE procedure [MetaData].[mmit_sp_DailyFailureReport]

———————— FAILED RECORDS ———————————————–

IF (OBJECT_ID(‘#tmpDailyReport’) IS NOT NULL)
DROP TABLE #tmpDailyReport

CREATE TABLE #tmpDailyReport(
   Date varchar( 12 )     Null,
   WebServerName         varchar( 150 ) NOT Null,
   Location         varchar( 150 ) NOT Null,
   cnt_RecsTotal    int Null,
   cnt_RecsFailed   int Null

INSERT INTO #tmpDailyReport(
   Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed
select ‘2008-01-01′,’Amsterdam-Server’,’Amsterdam’,1000,2
select ‘2008-01-01′,’London-Server’,’London Bridge’,50,0
select ‘2008-01-01′,’London-Server’,’London West Minster’,80,0
select ‘2008-01-01′,’Portugal-Server’,’Lisbon’,5,1


SET @tableHTML =
    N'<H3>Daily Failure Report</H3>’ +
      N'<div><H5>Execution Date: <font color="blue">’ + CONVERT(NVARCHAR(30), GETDATE(), 113) + ‘</FONT></H5></div>’ +
    N'<table border="1">’ +
    N'<tr><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>’ +
    CAST ( (

SELECT td = Date, ”, td = WebServerName, ”, td = Location, ”, td = cnt_RecsTotal, ”,
‘td/@bgcolor’ = CASE WHEN cnt_RecsFailed > 0 THEN ‘#FF3366’ END,
td = cnt_RecsFailed
FROM #tmpDailyReport
for xml path(‘tr’), TYPE

    ) AS NVARCHAR(MAX) ) +
    N'</table>’ +
    N'<br/>’ ;

drop table #tmpDailyReport

declare @RecipientList as varchar(max)
declare @MailqueryStr as varchar(100)
declare @FromStr as varchar(100)
declare @rslt as varchar(400)
set @RecipientList = ‘’
set @FromStr = ‘’

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘BizTalk Automated Emails Profile’,
    @recipients = @RecipientList,
    @body = @tableHTML,
    @body_format = ‘HTML’ ,
    @subject = ‘Daily Failure Report’ ;


The Magic is done here:

SELECT td = Date, ”, td = WebServerName, ”, td = Location, ”, td = cnt_RecsTotal, ”,
‘td/@bgcolor’ = CASE WHEN cnt_RecsFailed > 0 THEN ‘#FF3366’ END,
td = cnt_RecsFailed
FROM #tmpDailyReport
for xml path(‘tr’), TYPE

To understand it, break it down, this query above generates an XML document, that we inject into the HTML variable. Lets see what it looks like with the for XML statement.

I run the Query below, omitting the for XML path statement:




Now with the XML Statement. I tell the statement that the path is ‘tr’:



This means that for every Column a root element called ‘tr’ is created, and then the column data and name is injected. We can then decorate this output into a nice HTML page, as the output is just an HTML Table, and that is what we do here.


We then just concatenate the output from our HTML table generator code to the above statement with a Cast.


With a bit of an imagination, you can generate HTML with SQL Server, since they both tag based languages, so I hope this article will bring colour and life to your SQL email notifications.

Note: You do not need the Type declaration in the FOR XML clause, so

for xml path(‘tr’), TYPE

can be

for xml path(‘tr’)

Just a matter of personnel choice.

  • Uncategorized

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s