AHD04116: A duplicate record was encountered

Document ID : KB000031432
Last Modified Date : 18/09/2018
Show Technical Document Details
Introduction:

Description:

When attempting to create a record in CA Service Desk Manager (CA SDM), such as adding a classic workflow task to a ticket category, the system returns the following error:  "AHD04116:A duplicate record was encountered. Insert or Update failed. " 

In the STDLOGs, a message similar to the following is seen:

SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=2627 SQL State=23000] Violation of PRIMARY KEY constraint 'XPKwftpl'. Cannot insert duplicate key in object 'dbo.wftpl'. The duplicate key value is (401824). 

Environment:

This issue can be present in:

-SDM 12.7
-SDM 12.9
-SDM 14.1.X

Cause:

This warning message means that the system is attempting to create an ID (or key value) for the new record using an ID value that already exists on another record in the same MDB table.  

For each attempt made to create the new record, you will see the ID (or key value) increase by one (1) in the STDLOG messages. This would continue to occur each time an attempt is made to create a new record in that MDB table until the point when the system reaches the next value after the current highest ID value (or key value).

This occurs when the cached value for the next available ID (or key value) for that object is lower than the current highest ID value (or key value) in that MDB table for that object.

At startup, the system caches the next fifty (50) available IDs (or key values) for each object based upon the value for that object stored in the Key Control or KC table.  This MDB table holds the highest ID (or key value) for each object.


Solution:

To resolve this issue, you can run a simple command called "pdm_key_refresh" via a command prompt on the CA SDM server.  

This command can be used while the system is running and will not interfere with the use of the Service Desk Manager system.  

The function of this command is to refresh the cache for the next set of available IDs (or key values) for each object.  

When running this command, the data in the Key Control table will be dropped, and then when the first attempt to create a record for an object is made, the system will create a row for that object in the Key Control table based on the highest current ID (or key value) on the database MDB table for that object. 

Use Case Example:

When attempting to add a classic workflow task to a change category, the following error is received:

"AHD04116:A duplicate record was encountered. Insert or Update failed. " 

 Looking in the STDLOGs, the following message is seen:

SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=2627 SQL State=23000] Violation of PRIMARY KEY constraint 'XPKwftpl'. Cannot insert duplicate key in object 'dbo.wftpl'. The duplicate key value is (401824). 

Looking at the Key Control table in the MDB database, we can see that the value there for the wftpl (workflow template) object is 401871.

Looking at the MDB database table for wftpl (workflow template), which is where the workflow tasks which are attached to categories are stored, the highest ID value is currently 402683.

This means that the value in the Key Control table is incorrect, as it is set to a value that is too low.  

After running the "pdm_key_refresh" command, look at the Key Control MDB table again and the row for wftpl (Workflow Template) is no longer there.

After creating a new record in the wftpl table by adding a classic workflow task to a change category, which works now, you can see the row in the Key Control MDB table created for the Workflow Template object with a proper value.

**IMPORTANT NOTE**
If the kc table is manually updated, the "pdm_key_refresh" command must be ran followed immediately by a recycle before any other updates are made to the database.

Instructions:
Please Update This Required Field