ALTER TABLE #ACCOMPLISHMENTS
ADD CONSTRAINT PKCURSOR PRIMARY KEY (PROGRAM_YEAR, ACTIVITY_CODE)
When you create a temp table, SQL server hashes the name so that there's no name collision. TempDB has the same rules as all other databases - no 2 objects have the same name.
If 2 connections both create a temp table with the name #tmpTable, they'll appear in the tempDB system tables looking something like this
When you do al alter table add constraint and explicitly set the name, SQL doesn't do that kind of hashing. Hence if two connections are running that proc, the first will run fine, the second will try to create a constrain with the same name as an existing one, and will fail.
What I would recommend you do is create a unique index, rather than a primary key. Index names are only unique at the table level.
CREATE UNIQUE INDEX idx_pkCursor ON #ACCOMPLISHMENTS
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass