Posted by Jeff Kish on 05/16/07 12:41

Hi.

I have a procedure with this in it (there are no other references to
asset_number_bak_tmp_pk in the procedure and it calls nothing else written by
me, just system calls or normal dml).

create table #asset_bak(
asset_number varchar(60) not null,
asset_desc varchar(100) null,
location varchar(40) null,
constraint asset_number_bak_tmp_pk primary key clustered (asset_number))

When I run the procedure, I get this message:

(1 row(s) affected)
Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19
There is already an object named 'asset_number_bak_tmp_pk' in the database.
Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19
Could not create constraint. See previous errors.

How can I find where else the system thinks this constraint exists?


I tried this but it only finds it in one place (one row in the result set),
i.e. my procedure:

select sysobjects.name, syscomments.text
from sysobjects, syscomments
where sysobjects.id = syscomments.id and
((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or
(lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))

Is this somehow a case where I need to do something dynamically, or purge some
information? I thought temp tables and their crony constraints disappeared
after the procedure exited.

thanks
Jeff Kish

[Back to original message]