Sending an HTML Table by Email With sp_send_dbmail

An HTML table turns a wall of email text into a report people actually read. Build the rows with FOR XML PATH so odd characters in your data cannot break the markup, then hand the finished body to Database Mail.

Why plain text reports get ignored

Picture the daily file size report. It lands in your inbox at 7 AM as forty lines of text that wrap badly. You glance at it, close it, and tell yourself you will read it later.

Later never comes. Then one morning a log file has grown ten times and nobody noticed. A small table with a header row and aligned columns gets read. It costs about ten lines of T-SQL.

The demo below uses a temp table with three made-up files. Two of them have names that cause trouble in HTML, and one has no size at all. Real data does this to you eventually.

The obvious way breaks quietly

Most people start by gluing strings together. Look at the output. The ampersand and the angle brackets go into the markup untouched. A browser would treat <Log> as a tag, so that cell would look empty. The third row is NULL, because one NULL size wipes out the whole concatenated string.

DROP TABLE IF EXISTS #DbFiles;
CREATE TABLE #DbFiles (
    FileId   int PRIMARY KEY,
    FileName nvarchar(100) NOT NULL,
    SizeMb   decimal(10,1) NULL);

INSERT #DbFiles VALUES
    (1, N'Data & Archive', 12.5),
    (2, N'<Log>', 2.0),
    (3, N'Temp', NULL);

SELECT N'<tr><td>' + FileName + N'</td><td>'
       + CONVERT(nvarchar(20), SizeMb) + N'</td></tr>' AS NaiveRow
FROM #DbFiles
ORDER BY FileId;

That is how a report goes wrong without any error. Nothing fails. The email just looks odd, and you find out from a confused reader.

Let FOR XML PATH escape the text

FOR XML PATH knows how to escape text. Name a column td and it writes a td element. Name the query PATH('tr') and every row becomes a tr. Look at the second result below. The ampersand turns into & and the angle brackets into < and >. The browser shows them as the characters you meant.

The first result shows a trap. Two columns with the same name get merged into one cell, so “Data & Archive” and 12.5 end up glued together. The empty [text()] column in the middle keeps them apart. It looks like a hack because it is one, but everyone uses it.

-- Same name twice: the cells merge
SELECT FileName AS td, SizeMb AS td
FROM #DbFiles
ORDER BY FileId
FOR XML PATH('tr'), TYPE;

-- An empty text node in between keeps them apart
SELECT FileName AS td, N'' AS [text()], SizeMb AS td
FROM #DbFiles
ORDER BY FileId
FOR XML PATH('tr'), TYPE;

One more thing to notice. Row 3 has no second cell at all. A NULL column simply disappears from the XML, so that row has one cell while the others have two. The table would look ragged. The next block fixes it by turning NULL into the text n/a.

Build the full body

Now wrap the rows in a table with a header. The ISNULL on the size keeps every row at two cells. Convert the XML to nvarchar(max) so it becomes plain text that Database Mail can send.

DECLARE @Rows nvarchar(max), @Body nvarchar(max);

SET @Rows = CONVERT(nvarchar(max),
    (SELECT FileName AS td,
            N'' AS [text()],
            ISNULL(CONVERT(nvarchar(20), SizeMb), N'n/a') AS td
     FROM #DbFiles
     ORDER BY FileId
     FOR XML PATH('tr'), TYPE));

SET @Body = N'<table><tr><th>File</th><th>Size MB</th></tr>'
          + @Rows + N'</table>';

SELECT @Body AS EmailBody;

The result is a complete table. Row 1 shows Data & Archive, row 2 shows <Log>, and row 3 ends with n/a.

Plan for the empty report

Some days there is nothing to report. A query that returns no rows gives you NULL from FOR XML, and a NULL joined to a string is NULL. Your email body would be empty. Send a friendly line instead. I filter on sizes above 100 MB here, and no file qualifies.

DECLARE @Rows nvarchar(max), @Body nvarchar(max);

SET @Rows = CONVERT(nvarchar(max),
    (SELECT FileName AS td, N'' AS [text()], SizeMb AS td
     FROM #DbFiles
     WHERE SizeMb > 100
     ORDER BY FileId
     FOR XML PATH('tr'), TYPE));

SET @Body = N'<table><tr><th>File</th><th>Size MB</th></tr>'
          + ISNULL(@Rows, N'<tr><td colspan="2">Nothing to report today.</td></tr>')
          + N'</table>';

SELECT @Body AS EmailBody;

Send it, then check what happened

The send call is below, commented out, because it needs a Database Mail profile and a real address. Replace both and set body_format to HTML. Without that setting your reader sees raw tags.

/*
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'YourMailProfile',
    @recipients   = N'you@example.com',
    @subject      = N'File size report',
    @body         = @Body,
    @body_format  = 'HTML';
*/

SELECT TOP (5) mailitem_id, sent_status, subject
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

SELECT TOP (5) event_type, description
FROM msdb.dbo.sysmail_event_log
ORDER BY log_id DESC;

DROP TABLE IF EXISTS #DbFiles;

A successful call only means the mail was queued. It does not mean it arrived. The first query shows each request and its status. The second one holds the error text when a mail fails. On my test server both lists are empty, because I never sent anything. Send yourself a test first and open it in the email client your readers use. Clients render tables differently.

Send yourself a test, look at it in your real inbox, and only then add the schedule.

A good email report is not every row, it is the few rows that matter.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.