DROP TABLE fails for temp table

Posted by StarBright on Stack Overflow See other posts from Stack Overflow or by StarBright
Published on 2010-04-15T01:13:46Z Indexed on 2010/04/15 1:23 UTC
Read the original article Hit count: 404

Filed under:
|
|

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] AS int)
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?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about drop