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

sql sql-server sql-server-2000 ado temp-tables
phoenies picture phoenies · Aug 13, 2010 · Viewed 43.5k times · Source

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

Jon Freedman picture Jon Freedman · Aug 13, 2010

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year? Expected Output: 01/01/2012 and 31/12/2012

Read More