Solve Errors During Database Synchronization in Microsoft Dynamics AX

Today, I would like to share a simple solution to a common problem. When you synchronize databases in Microsoft Dynamics AX and get error messages, you can resolve this error more easily than by deleting tables one by one—you can perform this step in bulk. This blog post takes you through the steps you need to take and shares a SQL script sample to make them work.

In Microsoft Dynamics AX, when administrators synchronize databases, they often see error messages like in the screen capture below. This kind of error typically occurs when you work with multiple tables. The reason for this error is that 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 AX. Therefore, during database synchronization, SQL Server presents an error because of the ID mismatch.

Solution: Delete the Affected Tables, then Synchronize Again

Delete the affected tables from the SQL database. Synchronize the tables once again from AX. This solves the problem.

To delete the tables, simply take the following steps:

• Open the SQL Server Management Studio.
• Open the database associated with the AX instance which is giving the above synchronization error
• Delete the affected tables.
• If there are only a few affected tables , you can delete them manually by selecting each table. Alternatively, you can use the “drop table <tablename>” command.
• If a large number of tables is affected, you may want to delete them in bulk. However, SQL Server does not provide this option by default. In addition, you can select only one table at a time. Using the Shift or Ctrl keys for selecting multiple tables does not work. This is how you perform a bulk deletion.

• Open a new query window on the database as shown below.
• Copy and paste the SQL script below the screen image for bulk deletion of tables.
• Execute the script.

SQL script for bulk deletion of tables

select name into #tables from sys.objects where name like 'BPM%'

while (select count(1) from #tables) > 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

select @tbl = name from #tables where name like 'BPM%'

set @sql = 'drop table '+ @tbl

exec(@sql)

delete from #tables where name = @tbl

end

drop table #tables;

This SQL script identifies the tables by using table names starting with a specific prefix. In the example above, I have used “BPM%,” which results in the deletion of all tables whose name starts with the prefix “BPM.”

If you have other business scenarios where you would like some guidance from our business integration and data management experts, please get in touch with us.

If you're considering migrating from Dynamics AX to Microsoft Dynamics 365 Finance & Supply Chain Management but are unsure about what to expect and how to prepare for a data migration, we recommend reading our 'Ultimate Guide to Data Migration' from the link given below.