CODEWITHSUNDEEP
sql-server
Bob Clegg
Bob Clegg

Reputation: 563

SqlServer sp_send_dbmail send query result from local temp table

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

Upvotes: 4

Views: 11751

Answers (4)

ashok as
ashok as

Reputation: 11

You can use a global temp table like ##temp_table. Just don't use a local #temp_table like #this_table. sp_send_dbmail executes in a different context and won't know about the local temp table.

Upvotes: 0

CxFusion3mp
CxFusion3mp

Reputation: 83

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.

Upvotes: 7

Vaishali
Vaishali

Reputation: 9

I just discovered a great solution for file attachments with SQL sp_send_dbmail Stored procedure.

If we want to send output of any query as excel attachment (Where TABLE1 can be a temp table or permanent table in DB)

SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1

Declare a VARCHAR(MAX) @VAR variable which will have HTML TABLE attribute TABLE

Iterate through the rows of table to be send as attachment , wrap each rows column data in HTML TR TD attributes and append it to @VAR and PRINT it in @query parameter of sp_send_dbmail

@VAR = 
TABLE
TR 
TD COLUMN1ROW1DATA /TD
TD COLUMN2ROW1DATA /TD
TD COLUMN3ROW1DATA /TD
/TR
/TABLE

@query = 'PRINT ' + @VAR

This works like a charm and we can also add formatting to this HTML table which is visible in excel file.

Upvotes: 0

Jason W
Jason W

Reputation: 13209

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.

Upvotes: 3

Related Questions