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

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:

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:

Result:

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.

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.

Uncategorized

Romiko Derbynew

Generating professional email notifications with SQL

Introduction

SQL Code

Analysis

Conclusion

Leave a Reply Cancel reply

Introduction

SQL Code

Analysis

Conclusion

Share this:

Like this:

Related

Leave a Reply Cancel reply