0

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 = 'jXXn.XXXn??@yXXXXXX.com;XXXXXXXX1@gmail.com',           
 @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?

0
0

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.

0

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 = 'someone@domain.com'
    ,@subject = 'This is my Subject'
    ,@body = @body_html
    ,@body_format = 'HTML'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.