Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Send email in a tabular format using SQL Server database mail

MSSQLTips author Bru Medishetty By:   |   Read Comments (43)   |   Related Tips: More > 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.

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 = 'bruhaspathy@hotmail.com', -- 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:

how to send an email from sql server with data in a tabular format

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 = 'bruhaspathy@hotmail.com', -- replace with your email address @subject = 'E-mail in Tabular Format' ;
Next Steps


Last Update: 3/31/2011


About the author
MSSQLTips author Bru Medishetty
Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, October 15, 2014 - 11:52:25 AM - Dave Read The Tip

Really nice example, thanks. 


Tuesday, June 10, 2014 - 7:08:12 AM - seanin Read The Tip

Where in the code do you place the IF exists statement, I am having bother including it


Wednesday, March 05, 2014 - 11:04:04 PM - adi_ID Read The Tip

thanks for sharing. This article is really help me out!


Wednesday, February 26, 2014 - 11:14:01 AM - Brad Brewer Read The Tip

The below SP is ran to return a large amount of data that is sent via email using sp_send_dbmail however the code returned below when opened just as HTML looks fine but when sent via sp_send_dbmail does not it randomly changes the font, adds </td> and <td> tags causing the HTML email to look bad Anyone have any ideas on how this can be corrected or what is causing it?

 

DECLARE @query NVARCHAR(max)

SET @query =
 N'<table>' +
 N'<tr><td colspan = 3><H2><font style="font-family: Helvetica,Arial,sans-serif;">New Vendor Added</font></H2></td></tr>' +
 N'<tr style="text-align: left; background-color: rgb(53, 78, 153);"><td><font style="font-family: Helvetica,Arial,sans-serif;" color="#ffffff"><b><u>VENDOR</u></b></font></td><td><font style="font-family: Helvetica,Arial,sans-serif;" color="#ffffff"><b><u>CREATED DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</u></b></font></td><td><font style="font-family: Helvetica,Arial,sans-serif;" color="#ffffff"><b><u>CREATED BY</u></b></font></td></tr><font style="font-family: Helvetica,Arial,sans-serif;">' +
 CAST(
 (SELECT TOP 10
 td = REPLACE(UPPER(REPLACE(pe.NAME,',','')),'''''',''''),     '',
 td = CONVERT(VARCHAR(19), pe.Created, 120),     '',
 --td = pe.Created,'',
 td ='<a href="mailto:' + su.email + '">' + UPPER(su.FirstName) + ' ' + UPPER(su.lastname) + '</a>'
 FROM TransitPersonnel.PobEmployer pe
INNER JOIN Setup.[User] su ON pe.AuditUserId = su.UserId
WHERE (Notified = 0 OR Notified IS NULL) AND pe.IsDeleted = 0
ORDER BY name
FOR XML PATH('tr'), TYPE) AS nvarchar(MAX)) +
N'<tr><td colspan=3><center><b>Powered by</b><center></td></tr>' +
N'<tr><td colspan=3><center><img style="width: 231px; height: 64px;"alt="NeceSea Logo" src="http://app.necessea.com/images/neclogosmall.png"><center></td></tr>' +
N'</font></Table>'

SELECT REPLACE(REPLACE(REPLACE(@query, '&lt;', '<'),'&gt;','>'), '&amp;', '&') 


Thursday, December 05, 2013 - 7:39:21 AM - Adnan Read The Tip

Juju,

You can use 'If Exists' sql server statement to check the records in database. Based upon the same you can edit the body of the email in the else part.

e.g.,

if exists(select)

else

set(body)

Let me know if this helps

 

 

-Adnan

 


Monday, December 02, 2013 - 2:57:56 AM - Juju Read The Tip

May I know how to handle blank records?  When the output is 0, the email is also blank.  I want that a note will appear like this if result is blank "Note: If above this text you don't see table of errors logged in the system, then there is no error to report. Otherwise, please coordinate with the right teams/individuals to investigate and resolve the errors."  


Friday, May 24, 2013 - 6:49:54 PM - Greg Talbot Read The Tip

Just wanted to say "thanks" for your post. This helped me make a great daily report for my business! EXACTLY what I needed. Thanks again!


Wednesday, May 01, 2013 - 10:37:59 AM - Abdul Anoop Read The Tip

Hello there,

 

Wonderful stuff. I appreciate. I need a small suggestion.

 

I want the output to look like the following:

Rec:1
Player Name:Rafael Nadal Ranking Points:12390 Country:Spain

Rec:2
Player Name:Roger Federer Ranking Points:7965 Country:Switzerland

Rec:3 and so on ...
This is because I am already using one EDI file of this format to push the data to outlook(designed via C#).
if I could get this format as the output then I can use Database mail concept and sent ignore Outlook and C# Apps.

I hope you understood my query.

Thank You in Advance,
Abdul Anoop

 


Tuesday, April 23, 2013 - 9:42:06 AM - Frank Read The Tip

Just what  I needed awesome post!


Tuesday, April 16, 2013 - 11:35:23 AM - vidyasagar Read The Tip

Thank you very much. Its really a welldone stuff.

 


Thursday, April 04, 2013 - 2:28:10 AM - kaname Read The Tip

great... :) thanks


Friday, March 22, 2013 - 2:42:58 PM - Sudhir Read The Tip

 

Bru - Please let me know how to handle the blank records returning from the query. I want to get a blank table with no rows but column name should exist.

Because of the blank records, i m getting a blank report/email each time. Could someone please help me in this regard?


Wednesday, February 13, 2013 - 3:41:59 PM - UltraAdventureGuy Read The Tip

As for cell alignment, I used CSS which I inserted in the @body parameter between then <html><body> tags. You also can change the fontsize and table border  this way as well

Works like a champ ....


Thursday, November 22, 2012 - 11:12:55 PM - Dinesh Read The Tip

I want be mail failure notiffcaition on

  

select * from msdb.dbo.sysmail_faileditems order by  last_mod_date desc
.If suggestion.Please let me know..

Friday, September 14, 2012 - 9:21:36 AM - Jacinthe Read The Tip

Thank you very much it's working just fine !!!  :)


Friday, September 07, 2012 - 4:35:46 AM - Roni Vered. Read The Tip

Thanks very much, an excelent post.

Very clear, presice and helpful.

I used it in order to send myself error messages of failed jobs - modified the query to msdb views and changed the XML scheme accordingly.

works like a charm.

 

Thanks again.

Roni Vered.


Sunday, July 29, 2012 - 3:02:35 AM - eRez Read The Tip

thanks - that did the job!!


Thursday, July 26, 2012 - 5:45:10 PM - mobsite Read The Tip

@eRez... It's ugly, but you can order by your CASE statement.  Hope I understood correctly...

 


DECLARE @xml XML

DECLARE @T_ServerData TABLE (ServerName varchar(20),TotalRequests INT,TotalSuccessfulLoads INT)

INSERT @T_ServerData
        ( ServerName ,
          TotalRequests ,
          TotalSuccessfulLoads
        )
SELECT 'ServerZero',0,0 UNION
SELECT 'Server10',10,1 UNION
SELECT 'Server50',10,5 UNION
SELECT 'Server20',10,2 UNION
SELECT 'Server20B',100,20 UNION
SELECT 'Server10C',1000,100

SET @xml = CAST((
        SELECT  ServerName AS 'td', '',
                TotalRequests AS 'td','',              
                CASE
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END AS 'td'         
        FROM @T_ServerData
        ORDER BY CASE
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END
        FOR XML PATH('tr'), ELEMENTS )
    AS NVARCHAR(MAX))

SELECT @xml


Thursday, July 26, 2012 - 3:19:51 AM - eRez Read The Tip

great post - really helped me!

however, in my specific case the 'order by' is a bit more complicated, let me know if what i'm asking for is possible at all:

lets say the table i'm selecting from has 3 fields: server name, total request, and succefull loads. 

what i want to deliver is the server name, total requests and % of successfull loads- meaning (successful-loads)/(total-request), and i want the results to be ranked by this calculation.

if i try this -

SET @xml = CAST((
        SELECT  ServerName AS 'td', '',
                TotalRequests AS 'td','',               
                CASE WHEN TotalRequests = 0 THEN 0
                ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END AS 'td'          
        FROM @T_ServerData
        ORDER BY TotalSuccessfulLoads
        FOR XML PATH('tr'), ELEMENTS )
    AS NVARCHAR(MAX))

the results are orders by the absolute number of successful loads, not the relative (%).

is there any way to order the list by the calculation?

cheers,

eRez


Friday, July 20, 2012 - 7:18:07 AM - Ravinder Pal Singh Read The Tip

Many-2 Thanx Buddy... I whave to work on the same assignment. It has made my work very easy


Wednesday, July 18, 2012 - 2:20:23 AM - Senturk Bilen Read The Tip

Wonderfull post. Thank you.


Friday, July 06, 2012 - 8:33:11 AM - Kemi Tobais Read The Tip

I am just so relieved to to find this solution, perfect for what am looking for. To handle the null value, I used ISNULL()

 

Thanks, great tip.


Tuesday, July 03, 2012 - 8:51:13 PM - mobsite Read The Tip

@DonF - the previous comment was a long delayed response to your issue of how to right align.  you're right, an extra step is required.

 


Tuesday, July 03, 2012 - 8:49:52 PM - mobsite Read The Tip

-- output temporary column alias for the XML type
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',[Ranking Points] AS 'tdalignright','', 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>'

-- finally, use string function REPLACE() to change final HTML output
SET @body = REPLACE(@body,'tdalignright','td align=right')


Tuesday, July 03, 2012 - 9:24:41 AM - Aleksandar Read The Tip

Hi,

This is amaizing, it's exactly what i was looking for. Everything works fine when i execute my code from the query window but there is a problem when i use the code in a trigger.

What i want to do i send an e-mail message when there is a certain update in the table but i keep getting a blank e-mail body.

I've read something about NULL values and from what i understood until now is that sometime @xml variable is returning a null value.

Is there a solution for my problem? What lines should i change?

Thanks in advance,

Aleks


Tuesday, April 24, 2012 - 10:32:23 AM - Willi Read The Tip

Thanks for this tip, 'twas very helpful for me!


Wednesday, April 18, 2012 - 12:10:35 PM - Scott Read The Tip

Great Post! This was very helpful.


Thursday, February 23, 2012 - 8:23:17 AM - Adrian Sullivan Read The Tip

Fantastic!! Thanks Bru

It's always nice to get what you are looking for in a nice complete post.


Friday, February 17, 2012 - 7:50:29 AM - Elliott Read The Tip
Brilliant post love it

Thursday, July 07, 2011 - 11:39:40 AM - DonF Read The Tip

Not sure what I'm missing here, when I modified the SQL to look like this

SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
       [Ranking Points] AS 'td align=right','', Country AS 'td'
FROM  #Temp ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

I get the following error.

Msg 6850, Level 16, State 1, Line 1
Column name 'td align=right' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

 


Saturday, May 21, 2011 - 10:33:15 AM - mobsite Read The Tip

@8080_Diver - It would not hurt a thing, and may even be a better method if you know that all of your columns are numeric.  However, if you do have text columns in your output, you might prefer to leave those as default (left-aligned) for readability, and only right-align  your specific numeric columns.  Strictly preference.


Saturday, May 21, 2011 - 5:44:03 AM - 8080_Diver Read The Tip

@mobsite,

What will it hurt if you use the <td align=right> for all of the tags?


Wednesday, May 04, 2011 - 3:38:43 PM - Krishna Read The Tip

@mobsite

Yeah I know that but was trying to see if we can add them programmatically without manually changing


Wednesday, May 04, 2011 - 3:29:57 PM - mobsite Read The Tip
@Donf/@Krishna - output is html, so just modify the <td> tags for your numeric colums to be <td align=right>

Wednesday, May 04, 2011 - 3:02:45 PM - Bru Medishetty Read The Tip

@ Donf and Krishna.

Since I do not write a lot of code (XML atleast) I am right now trying to figure out the code and will post another reply here as soon as I get a chance to do.

Thanks

Bru Medisetty


Wednesday, May 04, 2011 - 12:47:02 AM - Krishna Read The Tip

How do we right justify the numbers ?


Tuesday, April 12, 2011 - 9:55:32 AM - DonF Read The Tip

How would you right justify a numeric column, in this case the Rankings Points?  Thanks for sharing!


Tuesday, April 12, 2011 - 5:52:13 AM - RichB Read The Tip

@Ryan - in 2000 you don't need to - just use sp_makewebtask and email the results.  Much easier!


Tuesday, April 12, 2011 - 12:41:48 AM - Ryan Read The Tip

How would you replicate this functionality in sql2000 with the for xml explicit clause? I've had a go and didn't have alot of success.


Friday, April 01, 2011 - 2:00:12 PM - Bru Medishetty Read The Tip

Great !! Appreciate your feedback Christina..


Friday, April 01, 2011 - 11:55:13 AM - Christina E. Leo Read The Tip

Used this today!  Great tip, thanks!


Thursday, March 31, 2011 - 1:19:26 PM - Bru Medishetty Read The Tip

Yes, I agree. The tip was written and submitted before Federer dropped to #3..


Thursday, March 31, 2011 - 12:08:57 PM - mobsite Read The Tip

uh oh, better update the table... fed dropped to #3 a week or two ago!  ;)

good tip, thanx much.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.