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:
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.