DROP TABLE not working for temporary table

Question

I have a client application that creates a temporary table, does a bulk insert into the temp table, and then does some SQL using the table before deleting.

Pseudo Code:

open connection begin transaction CREATE TABLE #Temp ([Id] int NOT NULL) bulk insert 500 rows into #Temp UPDATE [OtherTable] SET [Status]=0 WHERE [Id] IN (SELECT [Id] FROM #Temp) AND [Group]=1 DELETE FROM #Temp WHERE [Id] IN (SELECT [Id] FROM [OtherTable] WHERE [Group]=1) INSERT INTO [OtherTable] ([Group], [Id]) SELECT 1 as [Group], [DocIden] FROM #Temp DROP TABLE #Temp COMMIT TRANSACTION CLOSE CONNECTION 

This is a bug with an error in the DROP statement:

Cannot delete table "#Temp" because it does not exist or you do not have permission.

I cannot imagine how this failure can happen without something else, but I do not see any other failures that occurred before that.

Is there something I am missing that can cause this?

Answer 1

I tested this on SQL Server 2005, and you can delete the temporary table in the transaction that created it:

 begin transaction create table #temp (id int) drop table #temp commit transaction 

What version of SQL Server are you using?

You can review why you drop the temp table altogether. The local temporary table is automatically deleted when the connection is completed. Usually there is no need to explicitly indicate it.

The global temporary table begins with a double hash (fe ##MyTable .) But even the global temporary table is automatically deleted if it does not apply to it.

Answer 2

perhaps something is happening in the session between them?

Try checking the table before resetting it:

 IF object_id('tempdb..#Temp') is not null BEGIN DROP TABLE #Temp END 

Answer 3

I think you are not creating a table at all because the statement

 CREATE TABLE #Temp ([Id] AS int) 

wrong. Please write how

 CREATE TABLE #Temp ([Id] int) 

and see if it works.

Answer 4

 BEGIN TRAN IF object_id('DATABASE_NAME..#TABLE_NAME') is not null BEGIN DROP TABLE #TABLE_NAME END COMMIT TRAN 

Note. Enter the name of the table where TABLE_NAME and the name of the database where DATABASE_NAME specified

Answer 5

Make sure you have not created a temporary table with the same name in another query window.