Dump table to email

Question

Dump table to email

Dominic Gagné

SSCrazy

Points: 2654
More actions
January 7, 2011 at 7:36 am

#245034

Hi
I want to dump the result of a select into a email to send as is.
Query is:
SELECT DET.orderItem,
DET.orderQty,
DET.orderNo,
ISNULL(SUM(PRE.itmQty), 0) AS QtyColise
INTO #TableTemp
FROM TB_vpmOrderDetails DET
LEFT JOIN TB_vpmPrecolisage PRe ON DET.orderNo = PRE.vpmOrder
AND DET.orderItem = PRE.itmCode
INNER JOIN TB_vpmOrder ON DET.orderNo = TB_vpmOrder.orderNo
WHERE orderQty > 0
AND TB_vpmOrder.orderCompleted IS NULL
GROUP BY DET.orderNo,
orderItem,
orderQty
HAVING SUM(PRE.itmQty) <> orderQty
OR SUM(PRE.itmQty) IS NULL
Result:
orderItem orderQty orderNo QtyColise
------------------ ----------- ---------------------- -----------
M3988 1 1008416 0
M6127 1 1008417 0
M5874 1 1008424 0
M3665 1 1008413 0
F5996 1 1008423 0
M4209 4 1008420 0
I want to email this result using Database Mail:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'someone@mail.com',
@body = *RESULT OF SELECT WITH COLUMN HEADER*,
@subject = 'Articles non colisés',
@profile_name ='DBMail';
how I do that?
thanks for your time and help

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

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

one of the many optional parameters sp_send_dbmail allows you to include the results of a query as either an attachment, or as part of the body of an email.

here's a simple example where i'm allowit the body of an html email to have the query results:

because i'm putting it into the BODY of an html email, note how i selected the constant ' ' as the last column in order to get some prettier formatting.

declare @body1 varchar(4000)

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

''

EXEC msdb.dbo.sp_send_dbmail

@profile_name='MyMail Profile',

@recipients='youraddy@somedomain.net',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT name AS [ name],type_desc,create_date,'' '' As [Filler ] from master.sys.objects where type=''U''',

@query_result_header = 1,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 0,

@query_result_no_padding = 0

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!

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 2

thanks a lot for your reply, it was really helpfull.

I have a problem with the formatting:

Query:

EXEC msdb.dbo.sp_send_dbmail

@profile_name='DBMail',

@recipients= 'somemail@mail.com',

@subject = 'Article non colisé',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT * from ##TableTemp',

@query_result_header = 1,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 0,

@query_result_no_padding = 0

result in mail:

Liste des articles non colisé 07 Jan 2011 10:48:56

Item Qtée PO QtéeColisée ------------------ ----------- ---------------------- ----------- M3988 1 1008416 0 M6127 1 1008417 0 M5874 1 1008424 0 M3665 1 1008413 0 F5996 1 1008423 0 M4209 4 1008420 0 (6 rows affected)

how can I format the output to be readable?

thanks a lot

Lowell SSC Guru Points: 323755 More actions · Answer 3

one of either two ways: send as plain text:

@body_format = 'TEXT',

or, as i mentioned before, add an extra column that will have a " "

@query = 'SELECT ##TableTemp.*, AS FILLER from ##TableTemp',

i think it looks a little better with no headers myself...no way to get rid of the dashes if you include header.

@query_result_header = 0,

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!

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 4

that did the trick!

thanks a lot once again for your help!