SqlServer sp_send_dbmail send query result from local temp table

Question

trying to output the result from a sproc with sp_send_dbmail (Thanks to Joe Stefanneli for solution). When using a global Temp table variable (##Summary) it works. When using a local Temp table variable (#Summary) it fails. From what I have read, the global variable option is not applicable here as the sproc will be called by a web application so there will concurrent references to the same temporary table.

Apparently the call to sp_send_dbmail is not 'inner context' so the local variable fails. Is there a solution to this?

Sproc flow: Create Table #Summary (fields...)

Select stuff into #Summary

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Me',
@recipients = '[email protected]',
@body = 'Test from Me',
@subject = 'Automated Test Message',
@query = 'select * from #Summary ' ,
@attach_query_result_as_file = 1, 
@query_attachment_filename = 'testing.csv',
@query_result_separator=','

drop table #Summary

thanks

Answer 1

Came across this during my own issues. A global temp table will work. Create your table with ## and query from it with the email sql and it will work.

Answer 2

Temp tables won't work because the mail function just queues up the email, so the table has to be available to a different process and connection. You could

1. Create a permanent table that has a timestamp and guid columns.
2. Rewrite you stored proc call to insert the #summary records into the new table with the same guid and the current time.
3. Change the query in the the db mail parameters to select from the new table hard-coded to your guid from step 2.
4. Write a quick SQL job to delete records older than one hour.