|
|
Posted by Jeff Kish on 05/16/07 13:13
On Wed, 16 May 2007 08:41:50 -0400, Jeff Kish <jeff.kish@mro.com> wrote:
>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
I got around the problem by removing the 'constraint name' clause.
I guess it is optional, but I still wonder is it 'wrong' to have a named
constraint on a temporary table, and shouldn't it of gone away with the table
at the end of procedure execution?
thanks for the illumination, shots, etc.
Jeff Kish
Navigation:
[Reply to this message]
|