VERIFIED SOLUTION i

Resolve error "Embedded SQl error: SQlSTATE = 23000 [Microsoft][ODBC SQl Server Driver][SQl Server]" when resequencing in Confirm

Product Feature: Customer Services

Issue

When attempting to resequence features the following error is encountered :

"Embedded SQL error: SQLSTATE = 23000 [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'enquiry_status_log_pkx'. Cannot insert duplicate key 
in object 'dbo.enquiry_status_log'. The duplicate key value is (XXXXXXX,Y)."

Cause

This is caused by an enquiry assigned to one of the features having been exported from an external source, and the max status log values being out of sync. This was identified as a software defect.

Resolution

UPDATED: July 18, 2017
Use the following SQL to select and identify any enquiries that have status logs that are out of sync :

SELECT * 
FROM   central_enquiry, 
       enquiry_status_log 
WHERE  ( enquiry_status_log.enquiry_number = central_enquiry.enquiry_number ) 
       AND ( central_enquiry.enquiry_log_number = 
             enquiry_status_log.enquiry_log_number 
           ) 
       AND central_enquiry.enquiry_log_number <> (SELECT Max( 
enquiry_status_log.enquiry_log_number) 
 FROM   enquiry_status_log 
 WHERE 
enquiry_status_log.enquiry_number = central_enquiry.enquiry_number) 


If there are records selected then contact PB Tech Support for the SQL to fix the issue.