SQL Server Using Sp_send_dbMail and Temp Tables

If you try to refrernce a temp table in use sp_send_dbmail in a script you'll notice can't reference the table. For example the below script will not work.

DECLARE @STMT VARCHAR(100)

SET @STMT = 'SELECT * FROM #TEMPTABLE'

SELECT TOP 10 * INTO #TEMPTABLE FROM SYS.SYSPROCESSES

exec msdb.dbo.sp_send_dbMail 'DBMail',

@recipients = 'test@test.com',

@Subject ='TEST',

@body ='TEST',

--@importance = 'High',

@Query = @STMT,

@Attach_Query_result_as_file = 1

The reason for this is the sp_send_dbmail doesn't execute in the same session, rather it will create its own spid to execute. It does this by issuing a "Execute as Login" under the account the sql server process is running under. Doing so does not allow you to access the local temp table from the previous session. An easy work around here is to use a global temp table which can be seen by all sessions. So the following statement will work.

DECLARE @STMT VARCHAR(100)

SET @STMT = 'SELECT * FROM ##TEMPTABLE'

SELECT TOP 10 * INTO ##TEMPTABLE FROM SYS.SYSPROCESSES

exec msdb.dbo.sp_send_dbMail 'DBMail',

@recipients = 'test@test.com',

@Subject = 'TEST',

@body = 'TEST',

--@importance = 'High',

@Query = @STMT,

@Attach_Query_result_as_file = 1