4

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 = 'me@mysite.co.nz',
@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

Bob Clegg
  • 553
  • 2
  • 9
  • 21
  • Yes, build the body based on the results instead of trying to attach the results. Or have the web application build the e-mail instead of having SQL Server do it. – Aaron Bertrand Oct 21 '14 at 20:32
  • Possible duplicate of [sp\_send\_dbmail will not send query results](http://stackoverflow.com/questions/1322281/sp-send-dbmail-will-not-send-query-results) – vacip Jul 04 '16 at 13:26

4 Answers4

7

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.

CxFusion3mp
  • 83
  • 1
  • 4
3

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.
Jason W
  • 13,026
  • 3
  • 31
  • 62
  • Hi Jason, I have now made permanent tables. The top half of the sproc writes to this parent child relationship. The bottom half retrieves the new ParentId and builds an Sqlstatement with a where clause containing the ParentID. This variable is used as the query parameter for the call to sp_send_dbmail. Your answer contains the essential fact that permanent tables must be used. – Bob Clegg Oct 22 '14 at 00:59
0

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.

Martin Evans
  • 45,791
  • 17
  • 81
  • 97
Vaishali
  • 9
  • 1
  • I'd like to see a working example, but I'm not sure that HTML table is what was required or asked for. – jumxozizi Sep 28 '16 at 08:12
  • 1
    In fact I can't find anything about this technique. It's not totally off-topic as you could query a local table to populate @VAR. I'd still like to see a link to the technique since I can't figure out how to get it to work. :-( – JoelCool Mar 16 '17 at 22:30
0

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.

ashok as
  • 11
  • 2