Concurrency problems with temporary tables in subsequent batches?

Question

I sometimes have a problem running the script. I have a problem when using an application (which I did not write and therefore cannot debug) that runs scripts. This application does not return the full SQL Server error, but simply a description of the error, so I don’t know where exactly the error occurs.

I have an error only with this tool (it is a tool that sends queries directly to SQL Server using the DAC component), if I run the query manually in the management studio, I have no error. (This error, moreover, occurs only in a specific database).

My query looks something like this:

SELECT * INTO #TEMP_TABLE FROM ANOTHER_TABLE GO --some other commands here GO INSERT INTO SOME_OTHER_TABLE(FIELD1,FIELD2) SELECT FIELDA, FIELDB FROM #TEMP_TABLE GO DROP TABLE #TEMP_TABLE GO 

The error I get is: #TEMP_TABLE is not a valid object

So for some reason, I suspect that the DROP statement is executed before the INSERT statement.

But AFAIK, when GO exists, the next statement is not executed until the previous is completed.

Now I assume this is not true in temporary tables ... Or do you have other ideas?

Answer 1

Your problem is most likely caused by the termination of the session before DROP TABLE , which causes the SQL Server table to be automatically deleted, or DROP TABLE is executed in a different session than other code (which the temporary table was created and used), as a result of which the table should not be visible.

I assume that stored procedures are not involved here, because it looks like you are just executing batches, since local temporary tables are also deleted when you exit a stored proc.

This article has a good description of the behavior of the local temporary table in Temporary tables in SQL Server :

You get households using local temporary tables; they are automatically discarded when they go beyond if explicitly discarded using DROP TABLE. Their scale is more generous than the Variable table, so you have no problems with references to them in batches or in dynamic SQL. Local temporary tables are automatically discarded at the end of the current session or procedure. Omitting it at the end of the procedure that created it can lead to scratching the head: the local temporary table created in the stored procedure or session is discarded when it is completed, so it cannot be referenced by a process called the stored procedure that created the table. This can, however, refer to any nested stored procedures executed by the stored procedure that created the table. If a nested procedure refers to a temporary table and two temporary tables with the same name exist at the time in which table the query is allowed against?

Answer 2

I would run SQL Profiler and check if your tool uses a single connection to execute all batches or if it disconnects / restores. It can also use a connection pool.

In any case, executing SQL packages from a file is so simple that you can quickly develop your tool and be better.