There is already an object named '##Temp' in the database

Question

I have a stored procedure on SQL Server 2000. It contains:
select ... into ##Temp ...
...
drop table ##Temp

When I run the stored procedure with ADO a second time, it prompts:
There is already an object named '##Temp' in the database.
Could anyone kindly tell me what's wrong?

Voile

can you not just use a table variable (select .. into @temp). then you dont need to worry about deallocating the table, etc – Pinzler

The table structure is dynamic. That's why I use temp tables. – Voile

Answer 1

You should re-write your stored proc to drop the temp table if it exists, then you won't ever have this issue

IF (SELECT object_id('TempDB..##Temp')) IS NOT NULL
BEGIN
    DROP TABLE ##Temp
END

Verlaverlee

The error is <There is already an object named '##Temp' in the database.> So the problem in <select ... into ##Temp ...> but not <DROP TABLE ##Temp> – Fodder

Yes, and if there is already an object named ##Temp you need to drop the original in order to create a new one... – Verlaverlee

Just out of curiosity, did this code only apply for SQL-Server 2000? Tried it in SQL-Server 2008 and was getting an error: Cannot drop the table '#jobsconsumed', because it does not exist or you do not have permission. – Coonan

I've had the object_id test fail when run in a scriptwide context and pass when you highlight-then-execute literally all the code. IS_OBJECT seems to not be volatile; it seems to record state at the beginning of script execution, and in a different way when you are highlighting-then-executing. – Hollyhock

Answer 2

You are using a global temp table as indicated by the ## at the beginning of the table name. This means multiple sessions can access the table.

It's likely that you have a connection open that created the table, but failed to drop it. Are you sure that the first ADO run actually drop the table. Could it have failed, or did the flow control in the procedure skip the drop statement?

You may want to test the procedure in SQL Server Enterprise Manager to see if it reports any errors.

Baseball

I can't drop it from Query Analyzer either. Maybe for the first several times something failed. I'll see. – Voile

Yes, I should have mentioned that only the creating connection can drop it. You can run SP_WHO2 to see if there are other connections that may be the owner. Unfortunately, it won't tell you which connection owns the file. Good luck. – Baseball

Answer 3

Since you chose to use a global temporary table ##Temp, it is visible to all SQL connections at any given time. Obviously, while the stored proc is running for one connection, a second connection comes in and tries to create yet another ##Temp but that already exists....

Use connection-local #Temp tables (only one #) instead.

Comrade

Erm...but I'm sure there's only one connection Private DBCon As New Connection, which I created. – Voile

Answer 4

Oh, it's all my fault. I called the SP twice through one connection by mistake.
That's why it always reports error when being called the second time.
Of course you won't know that by reading my description. Sorry guys...

Voile

Answer 5

For me this solution works :

IF (SELECT object_id ='#Temp') IS NOT NULL
BEGIN
   DROP TABLE #Temp
END

Treponema

That won't work. object_id is a function. It should be object_id('#temp') – Vondavonni