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

Introduction

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

emailreport

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]
GO
/****** Object:  StoredProcedure [MetaData].[mmit_sp_BizTalkDailyFailureReport]    Script Date: 09/21/2008 09:22:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–CREATE procedure [MetaData].[mmit_sp_DailyFailureReport]
–as

———————— 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
union
select ‘2008-01-01′,’London-Server’,’London Bridge’,50,0
union
select ‘2008-01-01′,’London-Server’,’London West Minster’,80,0
union
select ‘2008-01-01′,’Portugal-Server’,’Lisbon’,5,1

DECLARE @tableHTML  NVARCHAR(MAX) ;

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 = ‘Romiko@domain.com’
set @FromStr = ‘info@domain.com’

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

Analysis

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:

image

Result:

image

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

image

 

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.

image

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

Conclusion

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.

Advertisements
Uncategorized

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s