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?

Answer 1

Have you tried creating a user-defined function instead of a stored procedure?

Something like:

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

Answer 2

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 ;

Answer 3

apparantly, using the @from_address property causes some problems.... because, i tried it without specifying the from address, and it worked just fine, but when i tried it with the from address, it gave me an execution error,... apparantly, the SMTP server has some trouble with changing the default from_address of its own....

i think it needs modifying some settings of the SMTP security..

any help would be appreciated... thanx ali