VERIFIED SOLUTION i

Resolve "Receiver Error: Violation of PRIMARY KEY constraint 'sw_batch_recipient_pkx'. Cannot insert duplicate key in object 'confirm.sw_batch_recipient'." in Confirm Street Works

Product Feature: Street Works


 

Issue

Error message where aaaaaaaaaaaaaaaaaaaaaa is the organisation, xxxxxxxxxxxxxxxxxxxxxx is the works reference, y = sequence number :

Sender Organisation = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Works Reference = xxxxxxxxxxxxxxxxxxxxx, Sequence number = y 
Filename: C:\inetpub\wwwroot\EToNWebService\XMLFiles\Invalid\EToN_20150601150804.xml 
Receiver Error: Violation of PRIMARY KEY constraint 'sw_batch_recipient_pkx'. Cannot insert duplicate key in object 'confirm.sw_batch_recipient'. The duplicate key value is (9085520, 1255001). 
The statement has been terminated. 

Encountered when trying to process an incoming notice via the webservice.

Cause

This error usually arises when there is a conflict in seed numbers - that is, an incoming Notice is trying to use a seed number that is already used in Confirm.

This can be caused if the incoming notice has both a primary and copy recipients that are the same.

Resolution

UPDATED: April 20, 2018
To rectify this you will need to either remove one of the duplicate recipients OR increment the seed numbers for the Work Group that the Web Service User is assigned to.

To find the Web Service User you can check the 'Web.Config' file, which is normally found in: \Inetpub\wwwroot\confirm web service on the Confirm Server. Search the file for 'Username', and you should find an entry similar to the following:

<add key="Username" value="SBS" />

You will then need to check this user in Confirm (System Administration>Security>User Security) to determine the Work Group this user is in.

To update the seed numbers for this Work Group:

• Go to Street Works>Street Works Lookups>Street Works Seed Numbers
• Recall the Work Group into the form
• Check the 'Street Works Batch seeds - This range'. You need to update these by incrementing the 'Current' number by 1 (or possibly 2) numbers.

Once this has been done, try to reprocess the notice.