AHD04116: A duplicate record was encountered

Document ID : KB000031432
Last Modified Date : 31/01/2019
Show Technical Document Details
Introduction:

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 one of 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).

Depending on what type of ticket you are creating and what value the system is failing on, you will see a different value in the "The duplicate key value is" section of this error message.

Environment:

This issue can be present in:

-SDM 12.7
-SDM 12.9
-SDM 14.1.x
-SDM 17.x

Instructions:

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

If the value specified in the error message is a string or is otherwise definitely not an ID value, double-check where that string is being specified when creating your object - it is likely that the field where that value was provided requires a unique value. As an example, when creating a Change Category, the Code field is required to be unique - attempting to create a Category with a duplicate Code value will yield an AHD04116 error message until an unused Code value is provided. Examining both the "SQL Execute failed" and the actual insert statement on the line below it in the STDLOGs can help to identify which field contains the duplicate value - as an example, you might see "The duplicate key value is (SOME_STRING)" in the first message, and ..."|<code:string>SOME_STRING|"... in the insert statement directly below it, which indicates that the Code field contains the duplicate here.

It is also possible for the system to say that the object's ID value is a duplicate, which most often occurs when outside data was imported into the system (usually by pdm_load). If the ID value already exists in the database when Service Desk Manager attempts to create a new object, you will see the ID (or key value) increase by one (1) in the STDLOG messages for each attempt to create the object. 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 unused value after the current highest ID value (or key value). To resolve this issue, you can run the pdm_key_refresh -y command via an administrative 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.