help formatting result of sp_send_dbmail

Ofina · Jun 8, 2012

I'm trying to learn all the possible parameters of sp_send_dbmail. The below call is the closest I have got to what I want. It's showing a line of the result on one line of the email, so that's good (by the no padding parameter). And, I have thrown in a tab as the result separator and this makes it a bit more readable.

But, what I really want is for each column of data to show up under it's respective heading. I basically want the look of a grid result (don't care about gridlines though), but in the email and not as an attachment. I need it to be right in people's faces.

I would even be happy enough if I can set the width of each column to, say, 20 characters. Then everything would at least be under one another, even if the whole value was not displayed.

exec MSDB..sp_send_dbmail
@recipients='myemail@email.com',
@query_result_no_padding=1,
@query_result_separator=' ',
@query=my query statement,
@subject='random subject line',
@body='The following items have been changed:

'

drlex · Jun 11, 2012

I had a similar issue, so now use html for formatting in conjunction with SQL server 2005's SELECT ... FOR XML ability.

Add to the dbmail exec

Code:

@body_format = html,

soi là, soi carré

Ofina · Jun 11, 2012

And then, how do I format it the way that I want, after changing it to html?

drlex · Jun 11, 2012

Here's some sample code:

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,'Novak Djokovic',11880,'Serbia'
UNION ALL
SELECT 3,'Roger Federer',10965,'Switzerland'
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 ='&lt;html&gt;&lt;body&gt;&lt;H3&gt;Tennis Rankings Info&lt;/H3&gt;
&lt;table border = 1&gt; 
&lt;tr&gt;
&lt;th&gt; Rank &lt;/th&gt; &lt;th&gt; Player Name &lt;/th&gt; &lt;th&gt; Ranking Points &lt;/th&gt; &lt;th&gt; Country &lt;/th&gt;&lt;/tr&gt;'    
 
SET @body = @body + @xml +'&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SS_Profile', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='html',
@recipients = 'ofina@tek-tips.com',
@subject = 'Tennis Squad for Office Tournament' ;

DROP TABLE #Temp

soi là, soi carré

drlex · Jun 11, 2012

(Oh, to remove grid lines in the table, change the border width to 0 with "<table border = 0>")

soi là, soi carré

Ofina · Jun 11, 2012

This is good. Can I make the contents a select statement rather than manually building a table?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

help formatting result of sp_send_dbmail

Ofina

Technical User

drlex

Technical User

Ofina

Technical User

drlex

Technical User

drlex

Technical User

Ofina

Technical User

Similar threads

Part and Inventory Search

Sponsor