	Product(s):	APM Implementation and Performance Management
	Version(s):	7
	Environment:	N/A
	Area:	N/A
	Subarea:	N/A

Problem Description

An APM user was recently updated to a new release of APM. Immediately after update the user started experiencing issues when trying to redraft POs, add PO Lines, receive PO Lines and process Invoices etc. Often the user was presented with a dialogue stating that the object could not be persisted.

When examining the logs we found that the object was failing to persist due to an attempt to duplicate the Primary Key for the PuchaseOrderAudit class

2022-07-14 14:57:21.5102Z Error SAS-PC-A085 username@domain.com Ivara.OTAssert [42] File: C:\a\1\s\framework\oqpers\database.cpp(3072) :
================= An error has occurred =============================================================
Failed Assert:
The operation you attempted cannot be completed because of a database error. Either the schema, code or cache files are out of date OR the deletion behaviour is incorrect.

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'PK__PURCHASEORDERAUD__388D7C6D'. Cannot insert duplicate key in object 'pro.PURCHASEORDERAUDIT'. The duplicate key value is (10).

This same situation appeared with several other classes in APM, such as PO lines, Invoice Batches, etc

Solution

Primary Keys are generated automatically within the APM database and must be unique for each object in that class. When the user was updated, they had done a dbtransfer of setting from a QA environment to the Production environment which resulted in the oq.nextobjectid being reset from its previous (Production environment value) to 1. The user was instructed to stop the services, have the dba run the apm stored procedure script: proc_common.sql and proc_ivara_apponly.sql. Finally the dba ran the stored procedure "Oq.restoreobjectid" which reviews and resets the nextobjectid for all classes.

Note - proper precautions including taking an outage, backing-up the database should always be followed when running this script on a Production database.

Post update user is unable to persist certain objects due to primary key duplication

Problem Description

Solution

See also