Learn more about SQL Server tools


Latest from MSSQLTips

Free SQL Server Learning

Get your SQL Server in shape this Summer with Spotlight on SQL Server

Encrypting Data with SQL Server

Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

Send email in a tabular format using SQL Server database mail

MSSQLTips author Bru Medishetty By:   |   Read Comments (50)   |   Related Tips: More > Database Mail

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.


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.

  [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'
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' ;

The HTML output from the above example looks like this:

<h3>Tennis Rankings Info</h3>
<table border="1">
  <th>Rank </th>
  <th>Player Name </th>
  <th>Ranking Points </th>
  <th>Country </th>
  <td>Rafael Nadal</td>
  <td>Roger Federer</td>
  <td>Novak Djokovic</td>

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:


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     

Tuesday, June 30, 2015 - 7:21:28 AM - Arjun Read The Tip

Hi All,

I need something like this to be sent an email. 













Below is the sql script i have written,
DECLARE @Sub varchar(500)
DECLARE @startDate VARCHAR(25)
Declare @Date VARCHAR(25)
Declare @Total_Num_OfEvents int
Declare @Events_Ended int
Declare @Events_InProgress int
Declare @Events_NotStarted int
Declare @EventState_Null int

set @startDate = (SELECT CONVERT(VARCHAR, dateadd(day,-1, cast(getdate() AS DATE))));
set @Date = @startDate
set @Total_Num_OfEvents = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_Ended = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='Ended' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_InProgress = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='In Progress' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @Events_NotStarted = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock) WHERE event_state='Not Started' AND start_datetime_tm LIKE (+'%'+@startDate+'%'));
set @EventState_Null = (SELECT COUNT(*) FROM MercuryBE.dbo.D_events(nolock)WHERE event_state IS NULL AND start_datetime_tm LIKE (+'%'+@startDate+'%'));


SET @ResulttableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} 
table{font-size:11px; border-solid:solid;} 
td{background-color:#F1F1F1; border:1px solid black; padding:3px;} 
th{background-color:#99CCFF;}</style>' +
N'<H3>'+'Event traded statistics for'+@startDate +'</H3>' +
N'<H3>Football Production Environment</H3>' +
N'<table border="1">' +
 </tr>' +
CAST ( ( select td=@Date,'',

SET @sub = 'Event traded statistics for'+@startDate 

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'SQLMailProfile',
  @recipients='TEst email',  
@subject = @sub,
@body = @ResulttableHTML,
@body_format = 'HTML',
@importance = 'high'

Could any one suggest, whther this is the right way to do it or not ?

Wednesday, May 27, 2015 - 11:53:13 AM - SteveG Read The Tip

Really helpful thanks! I'd previously been using something similar except I used a cursor to assemble the email html a row at a time. This way is much better :-)

Monday, May 18, 2015 - 3:32:55 PM - TomG Read The Tip

First - thanks for a very helpful post.


My question - I ran the query and formatted it as you suggested, but I can only get about 10-12 rows out in the email before the email breaks down. See below. In the last row, I get an exposed HTML tag and the email stops even though there are more rows.


Is this an inherent space limitation? What can I change to get all the rows?





Program Start Date

Missing Value Count

























Tuesday, May 05, 2015 - 8:33:13 PM - Gerardo Read The Tip

Hello, I want to send an email using the databasemail..I have this query and tried to use but for some reason I cant use the distict where I put it. When I made the query on sql it worked fine. Im not an expert so I dont really know whats wrong. 




SET @s = 'Listado de Requisiciones Aprobadas por Gerencia, favor de revisarlas. El listado fue generado el ' +' '+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) + 

' '

       SET @tableHTML = 

    N'<html><body><h1>Listado de Requisiciones Aprobadas</h1>' + 

    N'<table border="1" width="100%">' + 

     N'<tr bgcolor="gray"><td>CODIGO_REQ</td><td>LINEA</td><td>URGENCIA</td><td>DEPARTAMENTO</td><td>NAC/IMP</td></tr>' +  



       td=(distinct on T.EC_ID),'',

       td= U.LINE_NO,'',


       td=(SELECT     STRING_VAL

                            FROM          USER_DEF_FIELDS

                            WHERE      (PROGRAM_ID = 'VMREQENT') AND (LABEL IS NULL) AND (ID = 'UDF-0000039') AND (DOCUMENT_ID = T.EC_ID)),'',

       td=(SELECT     STRING_VAL

                            FROM          USER_DEF_FIELDS

                            WHERE      (PROGRAM_ID = 'VMREQENT') AND (LABEL IS NULL) AND (ID = 'UDF-0000018') AND (DOCUMENT_ID = T.EC_ID) AND 

                                                   (LINE_NO = PURC_REQ_LINE.LINE_NO)),''




WHERE     (PROGRAM_ID = 'VMREQENT') AND (ID = 'UDF-0000041') AND (T.STATUS = 'P') AND (T.SUB_TYPE = 'A2') and 


                    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'



EXEC msdb.dbo.sp_send_dbmail 


    @recipients='email address',

    @subject = @s,

    @body_format = 'HTML',



Monday, February 23, 2015 - 8:21:44 AM - samir Read The Tip
Declare @strbody as varchar(MAX)
Set @strbody = 'Dear Gilbert Kasdano, QR of NB 2219 is waiting for your approval.

QR Items

Item DescriptionQuantityPriceManufacturerAffiliate POLY 205 200 23.8 BLUESTAR SILICONES SANITA PERSONA SAL
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
@body = @strbody,
@body_format ='HTML', @recipients = 'samir.khouzam@indevcogroup.com', -- replace with your email address @subject = 'E-mail in Tabular Format' ;

Friday, February 13, 2015 - 10:16:25 AM - Jason Read The Tip

Thank you, this worked perfectly for me!

Tuesday, December 02, 2014 - 5:46:04 AM - Heinrich Kruger Read The Tip

HI there,

I ran 2 select statements within table format.

When I do not get any results back the mail is being sent but as a BLANK email? 

Is there a way to set default values within the table values if no results was returned?

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?



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;">' +
 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
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>' +

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

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


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.


if exists(select)



Let me know if this helps





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:

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

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 @T_ServerData TABLE (ServerName varchar(20),TotalRequests INT,TotalSuccessfulLoads INT)

INSERT @T_ServerData
        ( ServerName ,
          TotalRequests ,
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','',              
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
                END AS 'td'         
        FROM @T_ServerData
                    WHEN TotalRequests = 0 THEN 0
                    ELSE CAST(100.0*TotalSuccessfulLoads/TotalRequests AS numeric(4,2))
        FOR XML PATH('tr'), ELEMENTS )


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 )

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?



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'

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


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,


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'

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


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


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.


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