In this blog i will cover how to remove Database Synchronization Errors in Microsoft Dynamics AX 2012.This blog post takes you through the steps you need to take and shares a SQL script sample to make it work.

Sample Error

Cannot execute data definition language command on (). The SQL database has issued an error.

SQL Statement: ‘SP_RENAME’ ………

Field1 is already in use as Column name and would cause a duplicate that is not permitted.

Reason

The errors are due to KBs installation on different occasions. There are issue in some KB related fields where the Element id seems mismatched. They must match in SQL and AX2012 . In the database exists an older copy of the table with different Element IDs, which are not in sync with the Element IDs present in AX2012. Therefore, during database synchronization, SQL Server presents an error because of the ID mismatch.

Solution

To resolve database synchronization errors in Microsoft Dynamics AX 2012, first take data backup for tables from SQL Server Management Studio by generate script so that after changes in table we can import again. Next we need to Drop the table from SQL. Now go to AX2012 and synchronize the table by right clicking on table. Once sync is completed, check the table created in SQL. Next is to import the data back in the table by running the script.

Steps to fix errors

1-Go to SQL Management studio and generate data script to take data backup

2-Drop table from SQL (If unable to delete table due to referenced tables then rename the fields which have issues)

Drop table VENDPARAMETERS

3-Synchronize the table from AX2012 AOT

4-Check Table in SQL and import data using script we generated previously

Note: Same doing this we can also fix Issues in Views.

Once you are done with all the sync errors for tables and views Run the Full Sync from AX Environment.

Note: This approach is not recommended for production environment directly it must be tested first on development environment.

Thanks