phoenies phoenies - 1 month ago 7
SQL Question

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

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

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