It is very frustrating to have a table ID conflict which causes AX to drop a table with data that is needed. As anyone with AX 2012 experience will tell you, these things can be a major pain, even with two developers writing code at the same time. I think that over the years many people have shared SQL snippets, I have incorporated their ideas and added an additional step I have not come across before.
This script fixes both Table and Field IDs in SqlDictionary (data db) to match the AX code (Model db). Useful for after a database has been restored and the table or field IDs do not match. Instead of letting the database synchronization process drop and recreate the table, just run this SQL Script!
- Stop the AOS
- Always take the appropriate SQL backups before running this script
- Change database name <<AX2012DB>> to your own DB names (Ctrl + H)
- Add a reminder in your calendar to delete the table backups made: SQLDICTIONARY_BAK and SYSTEMSEQUENCES_BAK
- Start the AOS
- Sync the database within AX
Logic in the script:
- Check and fix any duplicates in SqlDictionary table.
- Find tables in SqlDictionary that have same name as in AOT but different ID and update SystemSequences.TabID = TableID in Modelstore. The SystemSequences table holds the next available record ID block for each table. The AOS actually consumes blocks of RecID’s, usually 256 at a time, and so the AOS must not be running.
- Find tables in SqlDictionary having the same name as in AOT but different ID and update the ID to match the ModelstoreID in SqlDictionary for Table and fields records.
- Fix the field ids in SQLDictionary which do not match
- Fix the field ids in SQLDictionary which do not match. There is a chance that the ID of a newly added field conflicts with an existing ID which is a field which is going away (not in the AX model). This is not typical but has happened.
You are able to review the records it will update by runing the CTE separately before running the whole command.
Objects that are new in AOT will get created in SQL dictionary when synchronization happens.