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]"

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.