Is it OK to use global temporary table?

Question

I have a query which conceptually can be described like this:

CREATE TABLE ##MyTable (
 -- rows  
)
INSERT INTO ##MyTable (...)
/*inserted SELECT */

WHILE ....
BEGIN
-- do some actions using data from temp table 
END 

EXEC msdb.dbo.sp_send_dbmail
    -- other data needed for email sending ...
    @query = N'select ... FROM ##MyTable;',

-- drop the temporary table
DROP TABLE ##MyTable 

So, i select some data to the global temp table, them work with it, them send email and finally delete this temp table.

This query is used as a task, which launches periodically to automate some processes in my DB.

The moment I doubt in - is global temporary table. If i plan to use this table (with such name) only in this automatisation script, can i be sure that there will be no collisions or some other similar bugs? It looks like it should not be, cause no users or program connections are going to use this table, so the logic is simple: my task launches once a week, creates this table then deletes it. But is it really so, or i miss some moments, and it is not a good idea to use global temporary table here?

PS: i've tried to use local temp. tables, but sp_send_dbmail returns an error (as far as i understand a table is deleted already when sp_send_dbmail launches):

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 208, Level 16, State 1, Server SERVER\SERVER, Line 1
Invalid object name '#MyTable'.

Answer 1

You are correct that a session temporary table can't be used with sp_send_dbmail. To quote from the docs, emphasis mine:

[ @query= ] 'query' Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

Global temporary tables can be created by any user so there is a possibility of a clash here. This can happen if one execution of the task takes too long and overlaps with the next run. There are three ways I might consider to resolve this.

1. Use the NEWID() function to create the name of the temporary table. This would ensure two executions of the script create tables with different names.
2. Use a permanent table with a column in it that is uniquely set by each execution of the script so that it can be referred to by the query passed to sp_send_dbmail.
3. I might consider using sp_getapplock to create a lock that other scripts could check to see if the table was in use. (Note if the script executions routinely overlap this may cause a backlog to build up).

Answer 2

1. Not using global temporary table. Convert your query output to HTML body, here may help you https://dba.stackexchange.com/questions/83776/need-to-send-a-formatted-html-email-via-database-mail-in-sql-server-2008-r2

2. Using global temporary table but consider to reduce the chance of collision

   a. try @Martin Brown's suggestion.

   b. if your while loop takes some time to finish, you can create a local temporary table for it first. Only dump the output to global temp table right before database mail. And drop it immediately after mail sent.

Answer 3

A global temporary table means that any other user can also try to create the same global temporary table. This would cause collision.

In most cases, creating and using a permanent table has served us well. You can lot of advantages with a permanent table. You can have a history of things done. If you think the data will grow, you can setup house keeping to delete data older than some days or weeks.

In our projects our guidance is: either create a "real" temporary table or a "real" permanent table.