Concurrency problems with temp tables in consequent batches?

I have sometimes a problem when running a script. I have the probelm when using an application (that I didn't write and therefore cannot debug) that launches t开发者_如何转开发he scripts. This app isn't returning the full error from SQL Server, but just the error description, so I don't know exactly where th error comes.

I have the error only using this tool (it is a tool that sends the queries directly to SQL Server, using a DAC component), if I run the query manuallyin management studio I don't have the error. (This error moreover occurs only on a particular database).

My query is something like:

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 somehow i suspect that the DROP statement is executed before the INSERT statement.

But AFAIK when a GO is there the next statement is not executed until the previous has been completed.

Now I suspoect that this is not true with temp tables... Or do you have another ideas?

---

Your problem is most likely caused by either an end of session prior to the DROP TABLE causing SQL Server to automatically drop the table or the DROP TABLE is being executed in a different session than the other code (that created and used the temporary table) causing the table not to be visible.

I am assuming that stored procedures are not involved here, because it looks like you are just executing batches, since local temporary tables are also dropped when a stored proc is exited.

There is a good description of local temporary table behavior in this article on Temporary Tables in SQL Server:

You get housekeeping with Local Temporary tables; they are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE. Their scope is more generous than a table Variable so you don't have problems referencing them within batches or in dynamic SQL. Local temporary tables are dropped automatically at the end of the current session or procedure. Dropping it at the end of the procedure that created it can cause head-scratching: a local temporary table that is created within a stored procedure or session is dropped when it is finished so it cannot be referenced by the process that called the stored procedure that created the table. It can, however, be referenced by any nested stored procedures executed by the stored procedure that created the table. If the nested procedure references a temporary table and two temporary tables with the same name exist at that time, which table is the query is resolved against?

---

I would start up SQL Profiler and verify if your tool uses one connection to execute all batches, or if it disconnects/reconnects. Also it could be using a connection pool.

Anyway, executing SQL batches from a file is so simple that you might develop your own tool very quickly and be better off.