Query Results as mail using sp_send_dbmail --- Text formatting issue

Question

Post reply

Query Results as mail using sp_send_dbmail --- Text formatting issue

AR-777

Ten Centuries

Points: 1288
More actions
September 29, 2009 at 12:56 pm

#98117

Hi,
I am generating a query using sp_send_dbmail as follows:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myprofile',
@recipients = 'email@yahoo.com',
@query = 'select top 2 * from customers',
@subject = 'Top 2 customer',
@attach_query_result_as_file = 0
this query executes normally but the problem is the text that i receive in the email is not formatted. I can read all values but they are all over the place in the email:
any one has any tips in getting the email in a proper table format like it shows in management studio.
hope it makes sense.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 1

You may want to play around with the following (from BOL):

[ @body_format = ] 'body_format'

Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

TEXT

HTML

Defaults to TEXT.

EDIT: Yeesh, that HTML arg doesn't work the way I thought it did (I was remembering the old xp_smtp_sendmail days). I have gotten around this before by specifying a CAST for each column to get them looking the way I want in the e-mail, although I am sure someone else will have a better solution than that.

MJM

AR-777 Ten Centuries Points: 1288 More actions · Answer 2

its still not formatted.

(Here is how it looks)

for

@query = 'select top 2 customerid, contactname, city from customers'

@body_format = 'html'

Result in the email:-

customerid, contactname, city --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ---------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------ ---- ALFKI Maria Anders Berlin ANATR Ana Trujillo México D.F.

Its the same thing for @body_format = 'text' but all words are at random places

so I am still trying to find out about how to make it work

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 3

ar-727381 (9/29/2009)
its still not formatted.
@query = 'select top 2 customerid, contactname, city from customers'

I know, sorry about that (see my Edit in my original response). How about:

@query = 'select top 2 CAST(customerid AS VARCHAR(10)), CAST(contactname AS VARCHAR(100)), CAST(city AS VARCHAR(50)) from customers'

MJM

AR-777 Ten Centuries Points: 1288 More actions · Answer 4

That works perfectly. just what I wanted.

Thanks a lot