Error message when you synchronize Microsoft Dynamics AX with a table in Microsoft SQL Server: "Cannot execute a data definition language command on [table_name]"

Applies to: Microsoft Dynamics AX 4.0Axapta Object Server

Symptoms


When you synchronize Microsoft Dynamics AX with a table in Microsoft SQL Server, you receive the following error message:
Cannot execute a data definition language command on table_name. The SQL database has issued an error.
Additionally, an error message that resembles the following is logged in the Event Viewer Application log on the Application Object Server (AOS) server:
Object Server 01: The database reported (session 1 (Admin)): [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.table_name' and index name 'I_xxxIDX'. The duplicate key value is (value1, value2). The SQL statement was: "CREATE UNIQUE INDEX I_xxxIDX ON table_name (column1,column2) "
Note
  • The table_name placeholder is a placeholder for the table name.
  • The I_xxxIDX placeholder is a placeholder for the unique index name.
  • The value1 placeholder and the value2 placeholder are placeholders for the duplicate key values.
  • The column1 placeholder and the column2 placeholder are the placeholders that represent the column names.

Cause


This problem occurs because the SQL Server database has duplicate records based on the unique indexes that Microsoft Dynamics AX tries to create or tries to re-create.

Resolution


Caution Before you remove any data from the Microsoft Dynamics AX database, you should understand why the new unique index is added. Typically, Microsoft Dynamics AX will add or remove indexes when there are changes to license keys or when you add or remove modules in the Microsoft Dynamics AX configuration form. Additionally, indexes may be added when developers add the indexes through the Application Object Tree (AOT) or through the regular upgrade process. If you are not sure why the index is added, you should not remove data.

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

To resolve this problem, either remove the duplicate records from the table to allow for the unique index to be added or determine whether the unique index should be added. To do this, follow these steps:
  1. On the AOS server, click Start, click Run, type eventvwr.exe, and then click OK.
  2. In the Event Viewer console tree, click the Application node.
  3. View the duplicate key error that appears first in the Application log. To do this, double-click the Error and source: Dynamics Server 0x item that appears first in the details pane.

    Note The x is a placeholder for the instance of the AOS service that returns the error message.
  4. Based on the error message that is displayed, replace the table name, the column name, and the index name with the values in the error message in the SQL Server script that you use in the next step.
  5. After the values are replaced from the actual error, run the following SQL Server script in SQL Query Analyzer or in SQL Server Management Studio.
    select count(*) as Duplicate_Record_Count, <column1>, <column2> from 
    <table_name> group by <column1>, <column2> having count(*) > 1
    Note Replace the placeholders with the appropriate information when you run the script.
  6. For each record that is returned in step 5, at least one duplicate record exists. The Duplicate_record_count column indicates how many records are duplicated by using those index values. If duplicate records are found, you must determine whether the records will be removed to add the unique index or whether the unique index should be added. To find all duplicate records, run the following script.
    select Distinct a.* from <table_name> a 
    join (select <column1>, <column2> from <table_name> group by <column1>, <column2> having count(*) > 1) b
    on a.<column1> = b.<column1> and a.<column2> = b.<column2>
    Notes
    • Replace the placeholders with the appropriate information before you run the script. You can obtain the information from the error message.
    • The Recid column can be used as a key identifier on each table to select or to remove records.
    • If you are unsure about how to run the scripts or interpret the results, you can work together with a qualified database administrator (DBA) and a support technician to decide how to process the results.
  7. Repeat step 5 and step 6 until the script in step 5 returns a null value that indicates that there are no more duplicate values and that the unique index can be added.
  8. If there is more than one unique index to be added, view the next duplicate key error in the Event Viewer Application log, and then repeat step 4 through step 7 until there are no more duplicate key errors in the Event Viewer Application log.