Solve Errors During Database Synchronization in Microsoft Dynamics AX

Apr 15, 2014 12:00:00 AM

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.

AX Database Sync 1

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:

  1. Open the SQL Server Management Studio.
  2. Open the database associated with the AX instance which is giving the above synchronization error
  3. 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.

AX Database Sync 2

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


declare @sql varchar(max)

declare @tbl varchar(255)

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

set @sql = 'drop table '+ @tbl


delete from #tables where name = @tbl


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 deletion of all tables whose name starts with the prefix “BPM.”

Did it work? Contact To-Increase

Share this message
About Author
Martien van Dam

Martien van Dam

Having started his career with To-Increase, Martien has honed and contributed his expertise across multiple roles and domains for 15 years now. Besides Microsoft, he’s also worked closely with all kinds of stakeholders in the ecosystem—including software vendors, CFOs, Accounts Payable (AP) clerks/managers, logistics managers—and is cognizant of their challenges and needs. Therefore, as a Sales Specialist for our Business Productivity and Accounts Payable domains, Martien brings a balanced mix of different market perspectives from various global associates to the table. On the personal front, Martien enjoys meeting people and experiencing cultures, and has visited around 75 countries. He's passionately involved in voluntary work and loves spending time with his three kids in the great outdoors. Martien holds a Bachelor of Commerce in International Marketing Management and Business Process and Project Management.

Get In Touch

Related Blogs

Aug 16, 2018 11:16:06 AM
Mar 21, 2018 7:05:27 AM
Dec 7, 2017 7:00:19 AM