DROP TABLE fails for temp table

sql-server temp-tables sql-drop
StarBright picture StarBright · Apr 15, 2010 · Viewed 41k times · Source

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

Nick Kavadias picture Nick Kavadias · Apr 15, 2010

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

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on …

Read More