Database Mail – HTML Formatting

I received a request that a user wanted to be notified when Unusual_Event_01 occurred. The information request was beyond the scope of a simple text e-mail, but didn’t require an excel file to be created and dropped.

I came up with a solution of submitting a HTML e-mail with sp_send_dbmail, but hand coding an HTML table with string concatenation didn’t appeal to me.

I have long since lost the website that was the original inspiration for this script, or I would give credit where credit is due.

The crux of the solution was using a FOR XML statement at the end of my query. I then named the element “TR” so that the individual lines of the query result would be ready for the table.

Here is the result – with some information changed for the purposes of this explanation.

DECLARE @RecordCount INT

IF OBJECT_ID('tempdb..#T_RecentUnusual_Event_01 ') IS NOT NULL
DROP TABLE #T_RecentUnusual_Event_01

CREATE TABLE #T_RecentUnusual_Event_01
(
[ID_Record] VARCHAR(11) NOT NULL
, [Data_Field_1] VARCHAR(25) NOT NULL
, [Data_Field_2] VARCHAR(25) NOT NULL
, [Data_Field_3] VARCHAR(50) NOT NULL
, [Data_Field_4] VARCHAR(50) NOT NULL
, [Data_Field_5] VARCHAR(50) NOT NULL
, [Data_Field_6] VARCHAR(50) NOT NULL
)

INSERT INTO #T_RecentUnusual_Event_01
SELECT
[ID_Record]
, [Data_Field_1]
, [Data_Field_2]
, [Data_Field_3]
, [Data_Field_4]
, [Data_Field_5]
, [Data_Field_6]
FROM Some_Source_Table

SELECT @RecordCount = COUNT(*) FROM #T_RecentUnusual_Event_01

IF @RecordCount > 0 --Just in case there were no records returned
BEGIN

DECLARE @tableHTML  NVARCHAR(MAX)

SET @tableHTML =
N'
<h1>Unusual_Event_01 occurred recently!</h1>
' +
N'
' +
N'

' +
N'

' +
N'

' +
CAST ( (
SELECT TD = [ID_Record],    '',
TD = [Data_Field_1],    '',
TD = [Data_Field_2],    '',
TD = [Data_Field_3],    '',
TD = [Data_Field_4],    '',
TD = [Data_Field_5],    '',
TD = [Data_Field_6]
FROM #T_RecentUnusual_Event_01
ORDER BY [ID_Record]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
<table border="1">
<tbody>
<tr>
<th>Employee SSN</th>
<th>Employee Number</th>
<th>Employee Name</th>
<th>Date LOA Started</th>
<th>Date LOA Ended</th>
<th>Date Updated</th>
<th>Last Updated By</th>
</tr>
</tbody>
</table>
' ;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'user@domain'
, @copy_recipients = 'dba@domain'
, @subject = 'Automated Email - Unusual_Event_01 occured recently'
, @body = @tableHTML
, @body_format = 'HTML' ;

END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'dba@domain'
, @subject = 'Automated Email - Unusual_Event_01 detection anomoly'
, @body = 'Process triggered - no results returned. How odd. Look into this.'
, @body_format = 'HTML' ;
END

IF OBJECT_ID('tempdb..#T_RecentUnusual_Event_01 ') IS NOT NULL
DROP TABLE #T_RecentUnusual_Event_01

Limitations:

  • Column names need to be hard-coded. This is possible to fix, I just was lazy at the time this was written.
  • My reasoning for the if statement is purely habit. Why risk sending a false positive to a user? The dba@domain address is my e-mail address. If I get one of these I know I screwed up, and this needs my attention. I could have wrapped this in a Try…Catch block and allowed it to fail into the catch, but hindsight is 20/20…

    This entry was posted in SQL Server Development and tagged , , , . Bookmark the permalink.

    Leave a Reply

    Your email address will not be published. Required fields are marked *