2

I am calling sp_send_dbmail and setting the query parameter. The resulting email includes the results of the query. I want the actual query (the value of the query parameter) to be appended to the email, so that the email will include the results and then the query itself at the end.

This will aid debugging.

3

1 Answer 1

2

You can set the body of the email to be the query that you want and send the results as an attachment. For example:

DECLARE @sql_cmd NVARCHAR(max) = 
'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
              WHERE DueDate > ''2004-04-30''
              AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Adventure Works Administrator',
@recipients = '[email protected]',
@query =  @sql_cmd,
@body = @sql_cmd,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

Sources:

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.