By: Bru Medishetty | Updated: 2011-03-31 | Comments (73) | Related: 1 | 2 | 3 | > Database Mail
Problem
Some of our SQL Servers contain tables with summarized data (summarized on a weekly / monthly / quarterly basis). Previously this data was emailed as a flat file attachment to the recipients. I was asked to convert this in such a way that the email itself contains the table data. This way the recipients would be able to look at the data in a tabular format, making it easier for readability. Hence we arrived at the question of how to send email from SQL Server with data in a tabular format.
Solution
First let me explain the code used in this tip.
I start by creating a temporary table and populating it with some sample data, it is a list of top Tennis players along with their Rank, Name, Ranking Points and Country. This temporary table is used only in the example, in real time the temp table would not be required, instead you would use the actual database table which contains the data.
The next section is responsible for converting the SQL Server table data that we created into a local variable "@xml". In the select statement, each of the columns is going to be displayed as table data and rows in the email.
In another local variable "@body" we add the required HTML tags and text that is displayed in the email, in this case "Tennis Rankings Info". Then we include the column headers of the table inside the email. Remember that these table headers (column names) can be changed as per your convenience. In the example code, the column names of the temp table have been used as table headers. In you wish to display "Player" instead of "Player Name", you can do that by changing the table header tag to <th> Player </th>. Finally the closing HTML tags are added.
Once the HTML has been formatted I send the email using the system stored procedure sp_send_dbmail found in the msdb database and finally I drop the temporary table.
In order to use the code below, against your database table, you will have to replace the table name and column names of your table wherever necessary. Also, you will need to change the mail profile name and email address that you want to use.
Send HTML Table via SQL Server Database Mail - Example 1
Here is the sample code.
CREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','', [Ranking Points] AS 'td','', Country AS 'td'
FROM #Temp
ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1>
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp
The HTML output from the above example looks like this:
<html> <body> <h3>Tennis Rankings Info</h3> <table border="1"> <tr> <th>Rank </th> <th>Player Name </th> <th>Ranking Points </th> <th>Country </th> </tr> <tr> <td>1</td> <td>Rafael Nadal</td> <td>12390</td> <td>Spain</td> </tr> <tr> <td>2</td> <td>Roger Federer</td> <td>7965</td> <td>Switzerland</td> </tr> <tr> <td>3</td> <td>Novak Djokovic</td> <td>7880</td> <td>Serbia</td> </tr> </table> </body> </html>
And this is how the email would look:
Send HTML Table via SQL Server Database Mail - Example 2
As another example, if you want to change this and select FirstName, LastName and EmailAddress from Person.Contact in the AdventureWorks database and order it by LastName, FirstName you would make these changes:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [FirstName] AS 'td','',[LastName] AS 'td','', [EmailAddress] AS 'td'
FROM Person.Contact
ORDER BY LastName, FirstName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Contact Info</H3>
<table border = 1>
<tr>
<th> First Name </th> <th> Last Name </th> <th> Email </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
Next Steps
- Make sure database mail is enabled on the SQL Server where this script is executed.
- Check out these other tips related to sending email from SQL Server:
- Sending email from SQL Server Integration Services (SSIS)
- Sending HTML formatted email in SQL Server using the SSIS Script Task
- Send Email from SQL Server Express Using a CLR Stored Procedure
- SQL Server 2005 Database Mail Setup and Configuration Scripts
About the author
Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-03-31
