DROP TABLE fails for temp table

Question

I have a client application that creates a temp table, the performs a bulk insert into the temp table, then executes some SQL using the table before deleting it.

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 failing with an error on the DROP statement:

Cannot drop the table '#Temp', because it does not exist or you do not have permission.

I can't imagine how this failure could occur without something else going on first, but I don't see any other failures occurring before this.

Is there anything that I'm missing that could be causing this to happen?

Answer 1

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

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

Which version of SQL Server are you using?

You might reconsider why you are dropping the temp table at all. A local temporary table is automatically deleted when the connection ends. There's usually no need to drop it explicitly.

A global temporary table starts with a double hash (f.e. ##MyTable.) But even a global temp table is automatically deleted when no connection refers to it.

Answer 2

possibly something is happening in the session in between?

Try checking for the existence of the table before it's dropped:

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

Answer 3

I think you aren't creating the table at all, because the statement

CREATE TABLE #Temp ([Id] AS int)

is incorrect. Please, write it as

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:Please enter your table name where TABLE_NAME and database name where it says DATABASE_NAME