October 4, 2018 at 12:56 pm
I recently installed sql 2016 ent on a dev box then moved the proc over. It is no longer failing the job when it comes accross a primary key violation. I made sure the index ignore_dup_key = false
2018-10-01 16:31:41.800 ========== Appointments ============= [SQLSTATE 01000]
2018-10-01 16:31:41.800 - Revenue.Revenue_Appointment [SQLSTATE 01000]
Msg 2627, Sev 14, State 1, Line 36 : Violation of PRIMARY KEY constraint 'PK__Revenue___9F7846851102DCAC'. Cannot insert duplicate key in object 'Revenue.Revenue_Appointment'. The duplicate key value is (SF, 1303225, 1533113, 3147111, 1). [SQLSTATE 23000]
Msg 3621, Sev 14, State 1, Line 36 : The statement has been terminated. [SQLSTATE 01000]
2018-10-01 16:36:09.007 - Liability.DeferredRevenue_Appointment [SQLSTATE 01000]
2018-10-01 16:36:10.507 - Liability.DeferredRevenue_AppointmentDiscount [SQLSTATE 01000]
October 4, 2018 at 12:59 pm
the db was pulled from 2008rs instance. the db is still in compatability level 100
October 4, 2018 at 12:59 pm
Snargables - Thursday, October 4, 2018 12:56 PMI recently installed sql 2016 ent on a dev box then moved the proc over. It is no longer failing the job when it comes accross a primary key violation. I made sure the index ignore_dup_key = false2018-10-01 16:31:41.800 ========== Appointments ============= [SQLSTATE 01000]
2018-10-01 16:31:41.800 - Revenue.Revenue_Appointment [SQLSTATE 01000]
Msg 2627, Sev 14, State 1, Line 36 : Violation of PRIMARY KEY constraint 'PK__Revenue___9F7846851102DCAC'. Cannot insert duplicate key in object 'Revenue.Revenue_Appointment'. The duplicate key value is (SF, 1303225, 1533113, 3147111, 1). [SQLSTATE 23000]
Msg 3621, Sev 14, State 1, Line 36 : The statement has been terminated. [SQLSTATE 01000]
2018-10-01 16:36:09.007 - Liability.DeferredRevenue_Appointment [SQLSTATE 01000]
2018-10-01 16:36:10.507 - Liability.DeferredRevenue_AppointmentDiscount [SQLSTATE 01000]
So you're not going to
a) Show us the code, or
b) Ask a question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2018 at 1:47 pm
Snargables - Thursday, October 4, 2018 12:56 PMI recently installed sql 2016 ent on a dev box then moved the proc over. It is no longer failing the job when it comes accross a primary key violation. I made sure the index ignore_dup_key = false2018-10-01 16:31:41.800 ========== Appointments ============= [SQLSTATE 01000]
2018-10-01 16:31:41.800 - Revenue.Revenue_Appointment [SQLSTATE 01000]
Msg 2627, Sev 14, State 1, Line 36 : Violation of PRIMARY KEY constraint 'PK__Revenue___9F7846851102DCAC'. Cannot insert duplicate key in object 'Revenue.Revenue_Appointment'. The duplicate key value is (SF, 1303225, 1533113, 3147111, 1). [SQLSTATE 23000]
Msg 3621, Sev 14, State 1, Line 36 : The statement has been terminated. [SQLSTATE 01000]
2018-10-01 16:36:09.007 - Liability.DeferredRevenue_Appointment [SQLSTATE 01000]
2018-10-01 16:36:10.507 - Liability.DeferredRevenue_AppointmentDiscount [SQLSTATE 01000]
A change in the data so that there is no PK violation?
October 4, 2018 at 2:29 pm
sorry, let me be more specific. I'm expecting the proc to error out due to the primary key violation on insert as it is doing in all of our other environments which are all 2008r2. It's failing on insert because the primary key violation. Am i missing something? Based on the log file it appears to simply keep on going.
October 4, 2018 at 3:06 pm
Snargables - Thursday, October 4, 2018 2:29 PMsorry, let me be more specific. I'm expecting the proc to error out due to the primary key violation on insert as it is doing in all of our other environments which are all 2008r2. It's failing on insert because the primary key violation. Am i missing something? Based on the log file it appears to simply keep on going.
And we are supposed to give you what, a wild shot in the dark while blindfolded? We can't see what you see which includes the code that you are asking about.
October 4, 2018 at 3:28 pm
Snargables - Thursday, October 4, 2018 2:29 PMsorry, let me be more specific. I'm expecting the proc to error out due to the primary key violation on insert as it is doing in all of our other environments which are all 2008r2. It's failing on insert because the primary key violation. Am i missing something? Based on the log file it appears to simply keep on going.
And that has everything to do with how the code is written - and without seeing the code there is no way anyone could help identify why that code is not exiting from the procedure when that error has occurred.
If the code does not have any type of error handling - then a PK violation is not a high enough error on its own to stop the batch from processing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 4, 2018 at 3:46 pm
assuming there are no code changes between the 2 procs it is possible someone set option XACT_ABORT globally on the old server.
dbcc useroptions -- if output shows xact_abort = set then it was set at server level
It is also possible that the connection to old and new servers are being done differently and the option is not being set on the new one and was on connecting to the previous one.
October 5, 2018 at 12:05 pm
sorry for being so ambiguous. I've got a job that’s calling a proc that has a simple insert that is trying to load a dup record relative to the primary key and the job isn’t failing. I created another job and mimicked the scenario and the job fails when the primary key violation occurs. For some reason the job in question that started this chain doesn’t fail when the primary key violation occurs. This same job fails in the other three environments its running it which are all sql 208r2 standard. I cant send the proc because it’s about 10k lines long and has proprietary code in it. Long and short though is there is a simple insert in there that is failing a primary key constraint and the job isn’t failing as a result on the sql 2016 ent instance. I’m going to recreate the job because I think it’s just a glitch. I originally scripted it from sql 2008r2 and moved it to sql 2016 ent. Below are the options on the new server 2016 enterprise the job in question is running from. I just did the install the other day. I’ll let you know what happens
@@OPTIONS= 5496
XACT_ABORT OFF
NUMERIC_ROUNDABORT OFF
CONCAT_NULL_YIELDS_NULL ON
ANSI_NULL_DFLT_OFF OFF
ANSI_NULL_DFLT_ON ON
NOCOUNT OFF
QUOTED_IDENTIFIER ON
ARTHIGNORE OFF
ARITHABORT ON
ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNING ON
CURSOR_CLOSE_ON_COMMIT OFF
IMPLICIT_TRANSACTIONS OFF
DISABLE_DEF_CNST_CHK OFF
October 5, 2018 at 12:23 pm
no error handling in the code It's just a bunch of truncate and inserts over and over again to build out 40-50 tables daily
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply