Looking at the scripts, I discovered that they both had something in common:

23 Sep 2005 8:46 PM

JT

Today, I needed to compare summary totals between 2 different (but related)
financial reports. Under normal circumstances, the reports are scheduled to
run in sequential order, but I fired them off simultaneously in seperate
Query Analyzer windows.

These reports have never failed in production... until today.

There is already an object named 'PK_AccountFD' in the database.
Could not create constraint. See previous errors.

Looking at the scripts, I discovered that they both had something in common:

ALTER TABLE #AccountFD ADD CONSTRAINT
PK_AccountFD PRIMARY KEY CLUSTERED
(
AccountNo
)

Apparently, the constraint that 2 tables in the same database cannot have
the same primary key name also applies to temporary tables. Simple fix, but
I'll have to be more careful when copying blocks of T-SQL for use elsewhere.
Show quote

:-/

23 Sep 2005 9:46 PM

Aaron Bertrand [SQL Server MVP]

> Looking at the scripts, I discovered that they both had something in
> common:
>
> ALTER TABLE #AccountFD ADD CONSTRAINT
> PK_AccountFD PRIMARY KEY CLUSTERED
> (
> AccountNo
> )
>
> Apparently, the constraint that 2 tables in the same database cannot have
> the same primary key name also applies to temporary tables.

Well, technically, the limitation is that a constraint name can only exist
once per database. So, you tried to create two constraints in tempdb with
the same name (doesn't matter that they are #temp tables or not).

I don't see any reason why you would need to explicitly name this
constraint, anyway, since it is only a transient object. Are you using
SELECT INTO to populate #AccountFD, or CREATE TABLE? If the latter, I would
just say

CREATE TABLE #AccountFD
(
AccountNo INT PRIMARY KEY CLUSTERED,
...
)

This will make your script a little smaller, and will let the system take
care of naming it (which it will do without the risk of conflict).

A

23 Sep 2005 9:53 PM

JT

I always give my indexes and constraints useful names when creating physical
tables, and I seem to have just carried that over to my temporary tables.
Thanks, for reminding me that SQL Server actually will name them for me.
Show quote

:-)

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uBcTdgIwFHA.3300@TK2MSFTNGP09.phx.gbl...
>> Looking at the scripts, I discovered that they both had something in
>> common:
>>
>> ALTER TABLE #AccountFD ADD CONSTRAINT
>> PK_AccountFD PRIMARY KEY CLUSTERED
>> (
>> AccountNo
>> )
>>
>> Apparently, the constraint that 2 tables in the same database cannot have
>> the same primary key name also applies to temporary tables.
>
> Well, technically, the limitation is that a constraint name can only exist
> once per database. So, you tried to create two constraints in tempdb with
> the same name (doesn't matter that they are #temp tables or not).
>
> I don't see any reason why you would need to explicitly name this
> constraint, anyway, since it is only a transient object. Are you using
> SELECT INTO to populate #AccountFD, or CREATE TABLE? If the latter, I
> would just say
>
> CREATE TABLE #AccountFD
> (
> AccountNo INT PRIMARY KEY CLUSTERED,
> ...
> )
>
> This will make your script a little smaller, and will let the system take
> care of naming it (which it will do without the risk of conflict).
>
> A
>