msdb.dbo.sp_send_dbmail temp table does not exist

Question

I have created a temp table and would like to email the results I am getting the following error.

Query execution failed: Msg 208, Level 16, State 1, Server PRODYCHDBX3\X3V7, Line 1 Invalid object name '#WODups'.

After the temp table is created I ran the following code.

 DECLARE @recordcount INT
 SELECT @recordcount = ISNULL(COUNT(*),0) 
 FROM #WODups
 IF (@recordcount > 0)
 BEGIN
 SELECT * FROM #WODups
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQL Mail',
 @recipients = '[email protected];[email protected]', 
 @query = 'SELECT FROM #WODups',
 @subject = 'Duplicate Work Order Production Tracking',
 --@attach_query_result_as_file = 1,
 @body =0;
 DROP TABLE #WODups

Why am I getting this error, and how may I resolve it?

Answer 1

Answer by scott-hodgin in comments:

You can't access local temp tables using dbmail. At a minimum, it would have to be a global (double ##) table. Even global tables can be a problem due to locking/blocking due to the fact the dbmail runs in a separate process than the process that creates the temp tables.

Table variables are not visible to the dbmail process either.

One option that I have used is to declare a cursor over the local temp table, fetch each row and 'build' the body of the email instead of trying to 'attach' a file. It depends on how much data you're extracting. Formatting can be a little tricky. After the body has been built, simply invoke the send_dbmail stored procedure.

Answer 2

Personally, when I email results (and it's not 100K rows or whatever) I like to mark it up with CSS and send it as HTML so it is readable. Shnugo has a great function for doing this over on Stackoverflow.

To use it, aside from his instructions, you can do something like so:

declare @body xml = 
 (select dbo.ufn_CreateHTMLTable (
 (SELECT FROM #WODups
 for xml path ('row'),elements xsinil)
 ,null,null,null))
--Maybe add some borders, Shnugo shows how to do it with his funciton too
declare @body_html varchar(max)
set @body_html = 
 '<style type="text/css" media="screen,print">
 .center
 {
 text-align: center;
 }
 table,th
 {
 border: 1px solid black;
 }
 table,tr
 {
 border: 1px solid black;
 }
 table,td
 {
 border: 1px solid black;
 }
 </style>' 
 + cast(@body as varchar(max))
 exec msdb..sp_send_dbmail
 @profile_name = 'SQL Mail', 
 ,@recipients = '[email protected]'
 ,@subject = 'This is my Subject'
 ,@body = @body_html
 ,@body_format = 'HTML'