Thanks chaps, I got my head around this in completely the wrong way based on something else that went wrong on this client's SQL box recently.
The Sproc is assuming the ##TEMP does not exist (i.e. not pre-deleting it) so it is entirely possible that it exists (although I can't think why ... and why the nth Sproc in the sequence, each with a uniquely named ##TEMP, would be the one to fail). The logs give no indication that that process failed the previous day (i.e. leaving the ##TEMP open), but the job (a BATCH file) has been failing every night since and not doing anything (first action is to log that it started, and that isn't happening).
I ran it manually and got a whole raft of "Access denied" ...
... there are also a whole raft of SQL DUMP files, and they have messages like:
Login failed for user 'MyDomain\MyServer$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.nnn.nnn]
network backups ( but client insisted as they just want to mirror the whole machine for DR and having local backups was causing massive network load) have also been failing a high proportion of the time - presumably the cause of those SQL DUMP files, and also permissions-related.
An unattended windows update ran shortly after the initial error, and rebooted the server. Dunno if that did something to change permissions, or if the Update was scheduled by their IT people who had been fiddling with other things ...
Anyway, upshot based on your feedback is that I think:
I'm inclined to name the CONSTRAINT (rather than let SQL choose one). I think that IF the error message had given me a NAME for the constraint I would have taken it seriously (given that the ##table, and thus the constraint, should not exist), whereas with a bland "Could not create constraint" I assumed was a side-effect error as clearly it was physically impossible (or so I thought) for a PKey Definition on a ##TEMP Create Table to raise an error [at the point of create table]
I don't understand how this error arose. There should be no problem creating the constraint (unless the table already exists, in which case a) SQL would be allocating a new, unique, constraint name and b) I would have got a "Cannot create - Table Exists" message instead
Am I missing something? (the SQL code is below)
Should not exist, but the code is not checking whether it does, or not. Manually running the SProc gave no error (so if it did exist at that time it certainly doesn't now). I would have expected a "Table exists" error, rather than constraint error
Is it possible that SQL generated the "same" unique key for the Constraint name?
I think so ... but maybe not!
The SProc calls some dynamic SQL involving OPENQUERY and I think a non-global #TEMP table wasn't in scope - but I may be imaging that, perhaps this was a solution to a problem 10 years ago and "we've always done it that way" since then
Yes-ish, I want the SProc to fail if there is a conflict on those columns. That would mean that a 3rd party has changed their schema, or the data-transfer has broken in some way. But as Scott said a UNIQUE index would do
Never thought of that, good idea, thanks. I can't believe I actually need a PKey, as such, on this table - just a check for unique-ness
CREATE TABLE ##TEMP_NameUniqueToThisSproc
[CHANGE_DATE] datetime NOT NULL,
[Col1] smallint NOT NULL, -- PKey
[Col2] varchar(21) NOT NULL, -- PKey
[Col3] int NOT NULL, -- PKey
[Col4] int NOT NULL, -- PKey
[Col5] varchar(31) NULL,
... lots of columns ...
[LastCol] int NULL,
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)