Fixing DB Synchronization Problems in Microsoft Dynamics AX 2012 [ENG]
Sometimes when synchronizing the database in Microsoft Dynamics AX 2012 we get some nonsense error messages. In my example, I’m getting an error stating that it can’t change the data type in a table field. But this field doesn’t exists on this table, same error says that is trying to convert a data field into a field of another table… weird.
This is an indicator that we are facing an ID problem with the table and/or the table fields. Unfortunately, we don’t have the proper tools to diagnose and fix this kind of problems, so we need to use dirty tricks like fixing the conflict directly in the database.
Of course, what I’m describing here is not recommended at all and it comes with a huge risk of loosing data and giving more problems that the ones is trying to fix if you don’t do it with care. Always test this in a dedicated test system and, if possible, try to avoid it.
That said, I will start taking a look to the sync status of the original table, the one linked to the error message shown in the sync log. On the AOT we can see the table name and its ID, the ID stored in the AX metadata.
Let’s see if this information is properly synchronized with the database searching the system table SQLDICTIONARY, where the system stores the status of the data dictionary objects in the SQL Server database, allowing the system to detect changes and fix them during the database synchronization process.
Searching the table by name and ID we can see that we can’t find it by Name, and by ID we are not getting the expected table… this is the one shown in the db-sync error message we got at the beginning.
Come back to AX and look for this table by name…
… looks like a different ID.
Back to the SQLDICTIONARY table, we can look for this ID too. Voilà! Here we can see that the table name and ID are incoherent. The ID of the table here is not the same stored in the AOT, and we can even find here two tables with the same ID… something is wrong.
At this moment is when, under our own responsibility, we can run some UPDATE to fix the TABLEID in the table records into the ID that is shown in the AOT. We need to be very careful to avoid updating unexpected records than may be wrong and have the same ID. We want to have the table and all its fields with the corresponding ID:
After fixing this, the table can synchronize without errors and we are all happy.
After the db synchronization, the missing fields are inserted into the SQLDICTIONARY and we have each table with its ID:
We should have a tool to fix this incoherency, but in the meantime, we can do the trick this way, with care ;)