SQL Server 2012 – msdb.dbo.sp_send_dbmail Temp Table Does Not Exist

database-mailsql serversql-server-2012temporary-tables

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?

Best Answer

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.

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Sql-server – Table Variable and Database Scope

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

Sql-server – Table Variable and Database Scope

Related Question