Understanding SQLDictionaryTable and AX data synchronization
Today we will talk about the objects' IDs and SQLDictionary table from SQL Server. Each object in AX (mainly classes, tables and the columns, with the emphasis placed on tables and columns right now, since that is what gets committed in SQL Server) gets an ID in order to identify them. In AX 2012 the ID is allocated at the moment of the object's creation in the ax system (either through a xpo import, new object in AOT, or model import).
Now, how does AX works with the tables in the SQL Server? The system will take the definition in the AOT and will commit that (the columns, their name, lenght, type) in SQL. So far, everything is going ok, but how is AX keeping track of what it has in the SQL Server? Matching the names of the objects would be enough? Let's see.
A table in SQL could be matched by it's name in the AX AOT. So, imagine that there is a change in a table's name, or easier, for the moment, a table column's name, going from colA to colB. The table, once changed, holds no reference that the column itself was renamed.
So, when the DD Sync process happens on the table, what would happen in SQL Server? The old colA column will be kept and the new colB will be created? The values from the inital column lost?
This is not a desired outcome. So, here comes into play a 'system' table, that will actually keep the information about what AX has written to SQL Server. The table is called SQLDictionary, and keeps the names of the AX objects, their names in SQL Server, and their AX IDs.
An object's ID in AX can't be changed manually by a developer in the IDE, so this value can be used to identify the object, and if it's name was changed. So now, we have an answer to the riddle above: if a column's name changes, AX will look in the SQLDictionary for an ID matching that column in AX, and depending on if it finds one, will either update an existing column (the old name to the new one) or create a new one (with the new name).
Now, how does AX works with the tables in the SQL Server? The system will take the definition in the AOT and will commit that (the columns, their name, lenght, type) in SQL. So far, everything is going ok, but how is AX keeping track of what it has in the SQL Server? Matching the names of the objects would be enough? Let's see.
A table in SQL could be matched by it's name in the AX AOT. So, imagine that there is a change in a table's name, or easier, for the moment, a table column's name, going from colA to colB. The table, once changed, holds no reference that the column itself was renamed.
So, when the DD Sync process happens on the table, what would happen in SQL Server? The old colA column will be kept and the new colB will be created? The values from the inital column lost?
This is not a desired outcome. So, here comes into play a 'system' table, that will actually keep the information about what AX has written to SQL Server. The table is called SQLDictionary, and keeps the names of the AX objects, their names in SQL Server, and their AX IDs.
An object's ID in AX can't be changed manually by a developer in the IDE, so this value can be used to identify the object, and if it's name was changed. So now, we have an answer to the riddle above: if a column's name changes, AX will look in the SQLDictionary for an ID matching that column in AX, and depending on if it finds one, will either update an existing column (the old name to the new one) or create a new one (with the new name).
Comments
Post a Comment