I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @name
set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.

This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:-PatP

Pat

That's exactly what defines my problem
If i run
DROP TABLE #teFotograferen

i get
Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

because the table was a temporary table and there's no way to get back in the scope where it was defined.

If i recreate the table and then drop it the constraint still remains in my database.

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
because it is not the same table

on the other hand

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
There is already an object named 'UniqueFields' in the database.

In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

It is not a design problem in my application, i just put some garbage in that i can't get out

That comment wasn't an accident.

One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

The solution to this problem is to:

a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
b) Create the constraint with a default name (which is unique for each execution).

This should get you far enough to find the next problem!

-PatP

[smacks forehead]

why would you need contraints on a temp table?

[/smacks forehead]

After a restart of the server the offending constraint was gone.

Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

Thanks for the advice and input.

---------------------------------------------------------------
Solution: plz use
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#TeFotograferen'))
BEGIN
DROP TABLE #TeFotograferen
END
else
begin
create table #TeFotograferen (...)
end

-- You may try the following

IF OBJECT_ID('tempdb.dbo.#TeFotograferen') IS NOT NULL
BEGIN
DROP TABLE #TeFotograferen
END


-- akhilesh