SQL Server 2014sp2 / SQL Server 2014 sp3 CU4 Primary Key Violation on Primary Key constraint on insert
Prod Env: SQL Server 2014 sp2
Test Env: SQL Server 2014 sp3 CU4
Prod and Test matches up. Recently updated Test Env to sp3 cu4. Since updating proc_a (within a SQL job) fails due the below error. Proc_a is using a cursor to insert into table_a. The errors are legit, there are duplicate records and I know how to handle the duplicates.
I want to understand why prod is working and test isn't. Suggestions?
Looking through the release notes, nothing sticks out.
SQL SERVER 2014 SP3 https://support.microsoft.com/en-us/topic/kb4022619-sql-server-2014-service-pack-3-release-information-64f341d3-2b74-0d86-e857-b9fd0775e493
SQL SERVER 2014 SP3 CU4 https://support.microsoft.com/en-us/topic/kb4500181-cumulative-update-4-for-sql-server-2014-sp3-a55b2271-fdae-8e82-115c-05214cc8dac0
ERROR: Msg 2627, Level 14, State 1, Procedure proc_a, Line 80 [Batch Start Line 7] Violation of PRIMARY KEY constraint 'PK_table_a'. Cannot insert duplicate key in object 'dbo.table_a'. The duplicate key value is (37, 56500909580000).
Have you checked your target(Job Properties>Targets) on the job? If they are in the same domain you might just be executing the job against the Prod server again rather than the Test Server. Other thing to check is the schedule. Typically people like to schedule their jobs (like hourly) on Test Servers more often than Prod, so perhaps there might be a reason in the T-SQL of the sproc that only causes duplicates when it's ran back to back rather than nightly?