sp_send_dbmail will not send query results

sql-server temp-tables sp-send-dbmail
Jean picture Jean · Aug 24, 2009 · Viewed 8.9k times · Source

I've tried every avenue on every damn forum suggested, but to no avail! Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient.

Step 2 of job is where failure occurs. Please help!

EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'MyDBA',
@recipients= '[email protected]',
@subject='Log Warning',
@query='SELECT * from #TempForLogSpace WHERE LogSpaceUsed >80

Answer

mrdenny picture mrdenny · Aug 24, 2009

You can't query from a temp table using database mail. The session that you used to create the temp table (step 1 of your job I assume) has been closed and a new session started when step 2 started. Because the session has been closed the table has been dropped (even if the table hasn't been dropped because it's a new session you don't have access to the other sessions temp table).

Either create a physical table and use that (either in the tempdb database or your database) or put the code which creates the output in the @query with the select * from #TempForLogSpace at the end (a stored procedure will be much easier to deal with in this case).

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on …

Read More