Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Have you tried this? social.msdn.microsoft.com/Forums/en-US/transactsql/thread/… –  Mr.Brownstone Feb 22 '13 at 16:19
    
Please can you give me an example? I'll accept it as the answer. –  cja Feb 22 '13 at 16:27
    
Did the example I gave you work? –  Mr.Brownstone Feb 22 '13 at 16:50
    
I've not tried but I don't think it will cos my query is a stored procedure and it outputs via a select statement. –  cja Feb 22 '13 at 17:08
add comment

1 Answer

up vote 2 down vote accepted

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 = 'danw@Adventure-Works.com',
@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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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