Passing stored procedure to sp_send_dbmail

Question

I am using sp_send_dbmail in SQL Server 2008 to send out the results of a query. I moved the query into a proc and am trying to use the proc in the sp_send_dbmail prcedure like so:

EXEC msdb.dbo.sp_send_dbmail 
@profile_name               = 'myprofile',
@from_address               = '[email protected]',
@reply_to                   = '[email protected]',
@recipients                 = '[email protected]',  
@importance                 = 'NORMAL', 
@sensitivity                = 'NORMAL', 
@subject                    = 'My Subject',
@body                       = 'Here you go.',
@attach_query_result_as_file= 1,
--@query_result_header      = 1,
@query_result_width         = 1000, 
@query_result_separator     = '~',
@query_attachment_filename  = 'myFile.txt',
@query                      = 'EXEC dbo.myProc'

I have also tried this using 4 part naming on the proc; with and without the 'EXEC', etc. It worked fine as a query, but I cannot seem to get it to work as a proc. Is this even possible?

Solution

You need to add the database context:

@execute_query_database = 'MyDatabaseName',

I just ran this with no problems against AdventureWorks2008:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',
    @recipients = '[email protected]',
    @query = 'exec dbo.uspGetManagerEmployees 5' ,
    @execute_query_database = 'AdventureWorks2008',
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;