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?

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

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.