Hi everybody,
I believe I did run into the problem before and even asked a question about, but I don't remember the answer. In the stored procedure I create a temporary table with a primary key (I used the same name for the constraint as the actual table this temp table represented, but with # at the end of the name). All worked well.
Yesterday I wanted to improve the performance of that procedure and so I slightly adjusted the columns in the PK to exactly match the PK from the table we were selecting the data from. I ran the tests several times (creating that temp table and selecting data into it). All worked fine as the ad-hoc query.
So we deployed my changes as a procedure and I got this error 'Error Message: Could not create constraint or index. See previous errors.'
Luckily I remembered I ran into this before. I removed the # from PK's name, re-run the whole process, got this error again. So this time I changed the PK name to the name matching the original PK but with 2 at the end and it worked, but then another procedure failed (which we didn't alter, but I did experiment with the query used by it yesterday too).
So, how can I name (and do I need to name) constraints on the temporary tables? There is
DROP TABLE IF EXISTS #tempTable preceding the temp table creation. It seems to be only failing when it's part of SP.
Thanks a lot in advance.