Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...If there has ever been a justification needed for access to the net during working hours, just referring to this site should suffice. Fantastic!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

help formatting result of sp_send_dbmail

thread183-1685351
Read More Threads Like This One
Ofina (TechnicalUser)
8 Jun 12 17:42
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 (TechnicalUser)
11 Jun 12 6:54
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 (TechnicalUser)
11 Jun 12 10:22
And then, how do I format it the way that I want, after changing it to html?
drlex (TechnicalUser)
11 Jun 12 10:42
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 ='<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 = '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 (TechnicalUser)
11 Jun 12 10:47
(Oh, to remove grid lines in the table, change the border width to 0 with "<table border = 0>")

soi là, soi carré

Ofina (TechnicalUser)
11 Jun 12 12:47
This is good. Can I make the contents a select statement rather than manually building a table?
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close