5

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
StarBright
  • 161
  • 1
  • 1
  • 5
  • A suggestion, move the create, bulk insert, and and drop statements outside of the transaction block. There's no reason or need to lock it, since no other connection can access it. – Philip Kelley Apr 15 '10 at 02:58
  • @Philip Kelley: That's not a bad idea, I will see if I can do that to limit the length of the transaction, but I can't see how that would be causing this issue – StarBright Apr 15 '10 at 03:41
  • The bug appears to be manifesting in the whitespace. ;) A complete repro script would shed more light on this. – Aaronaught Apr 15 '10 at 03:44
  • Yes, it will make the transaction shorter/tighter, and yes, this will have no impact on your problem. I'll side with Andomar: why try to drop the temp table, since it will get dropped anyway when the transaction is closed? None of the queries that actually use the table are failing, are they? (And yes, it would be nice to know what's going on, but as presented it is ultimately irrelevant to your process.) – Philip Kelley Apr 15 '10 at 13:58
  • Try create the temp table outside transactionscope, or use a table variable inside. – Independent Dec 17 '13 at 12:33
  • 2
    Did anybody actually find out why the error happened? I'm getting the same error connected with a login that has sysadmin. All those saying you don't need to drop the table are being completely unhelpful. The fact is the command is valid SQL and given a valid create table #Whatever (...) it should work regardless of whether you technically need to put it in or not. If the question was "my inner join is giving a syntax error" would the response be "you don't have to say 'inner' you can just say 'join'"? True but irrelevant because it should work so again wouldn't be solving the OPs problem. – Peter May 02 '14 at 12:37
  • Make sure you haven't created a temp table of the same name in a different query window. – FreeTheLemmings Dec 14 '18 at 08:37

4 Answers4

9

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
Nick Kavadias
  • 7,542
  • 2
  • 37
  • 46
  • I am adding some instrumentation to try to determine if the table is really gone, but in the meantime I can't see anything that could be causing this. There is no SQL occurring in the session other than what I have described and I am not getting any errors from the other statements – StarBright Apr 15 '10 at 03:40
7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
2
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

Joël Salamin
  • 3,538
  • 3
  • 22
  • 33
kamlesh kamble
  • 21
  • 1
2

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.

eKek0
  • 23,005
  • 25
  • 91
  • 119
  • Sorry for the confusion, I was changing the table name to protect the innocent and I typed it wrong... The actual table definition is semantically correct, I will update the question – StarBright Apr 15 '10 at 03:37