RE: [SQLDownUnder] Contraints on Temporary tables

[Simon Hall]

Jeremy,
I had, but from what I had read I thought that table variables were really suitable for rows of 100 or less.  In this instance it can range from less than 100 to several thousand.  Although it might be interesting to test it out.

Thanks for the input
Cheers
Simon
-----Original message-----
From: "Huppatz, Jeremy B" jeremy.huppatz@xxxxxxx
Date: Mon,  3 Sep 2007 14:13:47 +1000
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] Contraints on Temporary tables

> Simon,
> 
> Have you considered using table-valued variables as an alternative to
> temp tables?  The fact that they fall out of scope when your batch
> completes might be useful to you in avoiding the error message you
> reported. 
> 
> 
> Jeremy Huppatz | AUSADU Data Engineering (SQL Server/Data Warehousing) |
> EDS Australia
> Level 10, 108 North Terrace, Adelaide SA   5000
> t 08 8464 1739 | f 08 8464 2138 | m 0414 511 966 | e
> jeremy.huppatz@xxxxxxx
> 
> 
> -----Original Message-----
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Simon Hall
> Sent: Monday, 3 September 2007 12:45 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [SQLDownUnder] Contraints on Temporary tables
> 
> 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 'u> nsubscribe' as the subject. Powered by mailenable.com - List managed by htt> p://www.readify.net
> 
> 
> -- 
> Message  protected by MailGuard: e-mail anti-virus, anti-spam and content f> iltering.
> http://www.mailguard.com.au/mg
> 
> Click here to report this message as spam:
> https://login.mailguard.com.au/report/1tlusDnX03/7quJjB6fhX9ygAbv7Jkjvx/0
> 



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