Passing a stored procedure to sp_send_dbmail

Question

I am using sp_send_dbmail in SQL Server 2008 to send query results. I moved the request to proc and try to use proc in prceus sp_send_dbmail as follows:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile', @from_address = ' [e-mail] ', @reply_to = ' [e-mail] ', @recipients = ' [e-mail] ', @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 also tried this using 4 parts of the naming in proc; with and without EXEC, etc. It worked fine as a request, but I can't get it to work as a proc. Is it possible?

Answer 1

You need to add the database context:

 @execute_query_database = 'MyDatabaseName', 

I just ran this without problems against AdventureWorks2008:

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

Answer 2

Have you tried to create a custom function instead of a stored procedure?

Sort of:

@query = 'select something from dbo.myFunction()'