If you are facing below error while executing the stored procedure –
Msg 50000, Level 16, State 1, Procedure Name: ‘XYZ’ Severity Code: 14 Error State: 1 Error Number: 2627 Error Line:2833 Error Message: Violation of PRIMARY KEY constraint ‘PK_XYZ’. Cannot insert duplicate key in object ‘ABC.24_RPT’.
This is user defined error and you need to check that line of code to confirm what is exactly going on. This error will be due to unexpected records received in output of query. It won’t be easy to detect the record unless the procedure is broken in smaller statements and output of each is verified for uniqueness.
Suggestion is to take small pieces of stored procedure and execute them in test setup to verify the output. Then start building up the statements to catch the culprit table or data in it.
If you are familiar with SQL Profiler then execute stored procedure after starting profiler to get the exact data which due to which the error is thrown.