Unable to create new Non Working Days and/or error "Cannot insert duplicate key in object 'dbo.nonworking_day" in Confirm

Products affected: Confirm®
Product Feature: Asset Register
 

Issue

Unable to create new Non Working Days in Confirm®. Also the below error may be received in a DataWindow error form or within the logs:

Error - DataWindow error for nonworking_day "Violation of PRIMARY KEY constraint 'nonworking_day_pk'. Cannot insert duplicate key in object 'dbo.nonworking_day'. "

Cause

The ID value the system is trying to insert for a new Non Working Day record already exists in the system.
 
While creating a new Non Working Day a sequence number, which should be unique, gets allocated. Due to a problem with either seed numbering or sequence values (which one of these depends upon the version of Confirm you are running) a unique value has not been generated.

Resolution

UPDATED: January 24, 2020
If you are a Confirm OnDemand customer, please now raise a case with the Confirm Technical Support team. 

If you are a Confirm OnPremise customer:
  1. Check the existing maximum id number in the "nonworking_day" table (the following query may be run either in a Data Source or at database level):                SELECT MAX(nonworking_day_id) FROM nonworking_day
  2. To resolve the issue
  • In Confirm version 18.2 or lower to resolve the issue, update the " system_seed " table with latest sequence number for 'NONWORKINGDAYID'. To check the current seed number in "system_seed" table: 
SELECT * FROM system_seed WHERE seed_type ='NONWORKINGDAYID'
 
Now update the seed_no in system_seed table with MAX(nonworking_day_id) from the nonworking_day table (the result of the first query).
  • In Confirm version 19.0 or higher to resolve the issue, update the sequence "SEQUENCE_NONWORKINGDAYID" so that the next value it will issue will equal 1 plus the maximum id number in the "nonworking_day" table:
a. In a Data Source run

SELECT sequence_nonworkingdayid.nextval FROM DUAL

For example's sake we will presume that this has returned the value 41. We will presume that the maximum id number in the "nonworking_day" table (the result of the query in Step 1) is 126. The difference between these two value is 85.

b. Increase the sequence's value so that the next value it will issue will equal 1 plus the current maximum id number in "nonworking_day" table:

ALTER SEQUENCE SEQUENCE_NONWORKINGDAYID INCREMENT BY 85;
SELECT SEQUENCE_NONWORKINGDAYID.NEXTVAL FROM DUAL
ALTER SEQUENCE SEQUENCE_NONWORKINGDAYID INCREMENT BY 1;


The latter three lines will need to be run at database level (as opposed to within a Data Source).