Re: [SQLDownUnder] Contraints on Temporary tables

[noonie]

Greetings,

 

I think Matthew was suggesting that you don't name the constraint.

 

If you let SQL Server automatically name the constraint it should get a unique name, like the temp table actually gets, and your problem should go away.

 

--

Regards,

Neale NOON

 

On 03/09/07, Simon Hall <simon.hall@xxxxxxxxxxxxxxxxx> wrote:

Hi everyone,
Once agian I'm asking and not contributing.  I'll try and not make this too much of a habit.  I have two separate databases
with the same stored procedure in them.

There are two separate SQL jobs that run at diffrerent times.  Within the stored procedures a temporary table is created

CREATE TABLE #t1 (record_id_num int constraint imsoe_t1_key unique nonclustered (record_id_num))


From time to time one of the jobs fails typically when the two sql jobs run at the same time,  with the error:

There is already an object named 'imsoe_t1_key' in the database. [SQLSTATE 42S01] (Error 2714)  Could not create constraint. See previous errors. [SQLSTATE 42000] (Error 1750).  The step failed.


I assume the issue is related to the fact that a second temp table is being created with the same contraint name and hence causes the error.

I was under the understanding that when a temporary table is created then unless it is a global temp table it is only visible to that particular user connection or am I wrong.

Is there a way to enable the creation of the contraint so that SQL automatically assigns one automatically instead of having to explicitly name one?

Cheers
Simon Hall


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net