Sp_send_dbmail

Question

Sp_send_dbmail

Bhuvnesh

SSC Guru

Points: 59351
More actions
September 13, 2010 at 3:41 am

#98901

Hi
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SqlNotification' ,
@recipients = 'bkumar@abc.com',
@copy_recipients = '',
@subject = 'Test',
@body = '',
@body_format = 'TEXT',
@query = 'select top 3 * from sysobjects'
With the above code , we will get in mail but in unformatted(unreadable) manner. but i need results of query in decent/presentable manner. Is there any parameter or something, which can do it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

personally, i like queries as attachments;

at one point, i know i wanted all the data in the html of the email, so i build a simple CLR that formatted the dataset into an html table, and returned a SQLCHARS (varchar(max) of the formatted html table.

you might be able to do the same with FOR XML, , but i never tried that yet with multiple columns.

here's the code to add the query as an attachment, i can rustle up the CLRlater, but it's fairly straightforward.

declare @body1 varchar(4000)

set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

' '

EXEC msdb.dbo.sp_send_dbmail

@profile_name='gMail',

@recipients='lowell@somedomain.net',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.txt',

@query_result_no_padding = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323547 More actions · Answer 2

another possbility is to bcp the query to disk, then open it up and find/replace the delimiter(tab,comma?) with </td><td> and replace all CrLf with </tr><tr>;

append the leading and trailing table and tr tags and you've got it;

the advantage of the CR is that i used the column names to create a header row; but that could be part of the bcp so it's effectively the same.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

@lowell

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SqlNotification' ,

@recipients = 'bkumar@abc.com',

@subject = 'SQl 2008 email test',

@body = '',

@body_format = 'html',

@query = 'SELECT top 3 object_name(id) ,xtype ,uid ,info, status from sysobjects where xtype=''U''',

@query_result_header = 1,

@exclude_query_output = 0,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.txt',

@query_result_no_padding = 1

OUTPUT

xtype uid info status
- ----- --- ---- ------
#00C0EE5E U 1 0 0
#012B02B2 U 1 0 0
#01EA1CC1 U 1 0 0

Object's name are not in readable format.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lowell SSC Guru Points: 323547 More actions · Answer 4

that only happens if you run the statement in tempdb. those are real object names, but they were dynamically created by SQL;lots of object names in temp start like that:

from my temp:

#03317E3DU 100

#0425A276U 100

#0519C6AFU 100

try changing it to a specific database or master instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

thanks , its working

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)