SQL Server Email Query Results As A Table

Sending emails using DBMail is one of the most common requested tasks for the DBA. This could be alerts, reminders, results from a job or reports for users. As a DBA you should be familiar with querying a table and sending results as a CSV or Excel file as an attachment as well as formatting the email using basic HTML too.

For these examples we are assuming you have configured Database Mail and can execute the “sp_send_dbmail” procedure as well as some XML and basic knowledge of HTML. You also may need to be a member of the DatabaseMailUserRole database role.

Send Email Using sp_send_dbmail

The basic usage for sp_send_dbmail allows you to specify recipients, subject, body, formatting and to attach files too. With this simple format we can create emails with more static parameters. It’s likely though that the recipients, body and attachments will be dynamic and produced via SQL query or procedure.

EXEC msdb.dbo.sp_send_dbmail 
    @recipients='email@here.com;myemail@there.org',
    @subject = 'MySubject',
    @body_format = 'HTML',  -- or TEXT is default
    @importance = 'High',    -- importance flag: low, normal, high
    @file_attachments = 'c:\somepath\tofile.csv;D:\path2\file2.xlsx' -- files to attach
    @body = 'The Message body';

Simple HTML Table Using XML

We are using a variable to save the HTML content and then we will assign it to the body procedure parameter with the “body_format” set to HTML. We need the query to be formatted as HTML too so we are using the FOR XML statement to produce the table and rows.

We need to append the table to our variable so that means creating the HTML from top to bottom with the table data, or rows, being added via our SQL statement and then concatenated.

DECLARE @htmltable NVARCHAR(MAX)

SET @htmltable = N'
<h2>the title of this report or table data</h2>
<p>thjs is is the first paragraph describing the report.</p>
<table border="1px solid #ddd", cellpadding="8">' 

XML to HTML Table

We can use the FOR XML statement to provide the HTML elements of our table. We are creating the header (th) and then the columns (td) and rows (tr). This will need 3 SELECT statements, 1 for each element generated and they are encompassed within parenthesis. The first line is the header defined as “thead” in HTML.

(SELECT 'CountryName' AS th, 'FormalName' AS th, 'Continent' AS th FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',

Next we have the data rows and columns defined as “td” and “tr”. Notice the alias is “td” for each field and the entire statement is FOR XML RAW (‘tr’).

(SELECT
		[CountryName] AS td,
		[FormalName] AS td,
		[Continent] AS td
	FROM [Application].[Countries] FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'tbody'
	FOR XML PATH(''), ROOT('table')

The entire statement is encompassed in one SELECT and concatenated into the variable as a string. We then end the html with a table closing tag. I included the + concatenation symbol (db tales com) as this is how it is added to the variable.

+ (SELECT 
	(SELECT 'CountryName' AS th, 'FormalName' AS th, 'Continent' AS th FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',
	(SELECT TOP 100
		[CountryName] AS td,
		[FormalName] AS td,
		[Continent] AS td
	FROM [WideWorldImporters].[Application].[Countries] FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'tbody'
	FOR XML PATH(''), ROOT('table'))

+ N'</table>';

Email Query Results As A Table Source Code

Here is the complete code using the WorldWideImporters database for the data.

SET @htmltable = N'
<h2>the title of this report or table data</h2>
<p>this is is the first paragraph describing the report.</p>
<table border="1px solid black", cellpadding="8">' + 

(SELECT 
	(SELECT 'CountryName' AS th, 'FormalName' AS th, 'Continent' AS th FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'thead',
	(SELECT TOP 100
		[CountryName] AS td,
		[FormalName] AS td,
		[Continent] AS td
	FROM [WideWorldImporters].[Application].[Countries] FOR XML RAW('tr'), ELEMENTS, TYPE) AS 'tbody'
	FOR XML PATH(''), ROOT('table'))

+ N'</table>';

EXEC msdb.dbo.sp_send_dbmail 
    @recipients='mymail@somewhere.com',
	@subject = 'The Report',
	@body = @htmltable ,
	@body_format = 'HTML',
	@profile_name = 'Default Mail'

Check the Database Mail Queue

USE [msdb];

SELECT * FROM sysmail_allitems
SELECT * FROM sysmail_unsentitems
SELECT * FROM sysmail_event_log

EXECUTE dbo.sysmail_stop_sp ;
EXECUTE dbo.sysmail_start_sp ;
EXECUTE sysmail_help_status_sp ;

---