Note that the below was written in 2010 with AX 4.0 and 2009 in mind.
Sometimes table Id's will become out of sync between environments. The number one way that this is found out is when someone promotes tables that were created after other tables that are unpromoted to a new environment without the 'Import with ID values' checked and then other tables promoted with that check box checked on the import. Remember that AX manages and drives what is in SQL including indexes, fields, relations, etc.
About table Ids:
SQL uses Table Ids to uniquely identify tables in SQL. AX determines, stores, and manages table Ids, not SQL. For this reason, if table Ids need to be changed, they should be changed through AX. When a SQL refresh
Why it is important to have the table Id's in sync between environments?
Simple answer: SQL refreshes to lower environments are only possible when Id's are in sync. If they are out of sync, there are all sorts of issues that can pop up including data loss. Honestly, there is no reason that ID's should be out of sync as long as everything is managed properly. Be diligent or you're going to have a bad time.
When would you import code without table Id's checked?
If there are multiple development environments where developers are working and will ultimately be moving all code to one environment (e.g. 4 dev environments, 1 test), the id's from the test environment will be where the id's will be created.
How are Id's created in AX?
AX 2012 Ids will not be the same formula as below. AXUtil assigns base Ids at install and new ones regardless of layer increment off of a 6 digit number (eg 105067)
When tables, fields, classes, etc are saved in the AOT, they grab the next available Id based on the layer they are imported into. Each layer has 10000 ids that it can use. The below shows the values of the Ids that would be created based on the layer.
- BUS – 2xxxx (e.g. 20014)
- VAR – 3xxxx (e.g. 30014)
- CUS – 4xxxx (e.g. 40014)
- USR – 5xxxx (e.g. 50015)
There are multiple ways to take care of syncing environments but some methods may be better than others depending on the situation. If different data is needed to be retained in different environments, option 1 below cannot be taken as it would wipe out the data in the non-master environment when the tables are synchronized
- Syncing all environments based on a single, 'master' environment (more than likely PROD)
- Put a code freeze on all environments
- Export any code that is not in the 'master' environment
- Refresh environments' app folders with the 'master' environment's app folder
- Reimport unpromoted work back into the appropriate environments making sure that table Id's will not overlap between environments
- Manually change Table Id's in Notepad from XPO
- Try exporting the object (with id's), then edit the xpo in notepad to change the id, then reimport the object (with id's) from the edited xpo.
- Export the tables of interest (specific ones or an entire layer)
- Import the modified XPO with ID values
- Manually change Table Id's in AX via X++
- This can be tricky if you are trying to change an id to a value that is already set to another table. This approach could cause data loss in sync if not careful.
- Use the following methods to do change the table ids. They're pretty self explanatory.
- The most useful ones
- ReleaseUpdateDB::changeTableId(_oldTableId, _newTableId, _tableName);
- Changes the table Id from one value to another
- ReleaseUpdateDB::changeFieldId(_tableId, _oldFieldId, _newFieldId, _tableName, _fieldName);
- Changes the field Id from one value to another.
- Other options
- ReleaseUpdateDB::changeTableByName(_sqlName, _oldTableId, _newTableId);
- ReleaseUpdateDB::changeFieldByName(_sqlNameOld, _oldTableId, _newTableId, _fieldIdNew);
- ReleaseUpdateDB::changeTableByAOTName(_nameAOT, _oldTableId, _newTableId);
- ReleaseUpdateDB::changeFieldByAOTName(_nameAOT, _oldTableId, _newTableId, _newFieldId)
- I have some code to do this but you can check sample code here for some ideas. I thought it was pretty solid: http://dev.goshoom.net/en/2011/11/id-change/ Thanks to
SysDictTable dictTable;
DictField dictField;
TableId tableId;
FieldId fieldId;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryField;
setPrefix("Update of data dictionary IDs");
tableId = dictionary.tableNext(0);
ttsbegin;
while (tableId)
{
dictTable = new SysDictTable(tableId);
setPrefix(dictTable.name());
if (!dictTable.isSystemTable())
{
//Finds table in SqlDictionary by name in AOT, if ID was changed.
//Empty field ID represents a table.
select sqlDictionaryTable
where sqlDictionaryTable.name == dictTable.name()
&& sqlDictionaryTable.fieldId == 0
&& sqlDictionaryTable.tabId != dictTable.id();
if (sqlDictionaryTable)
{
//Updates table ID in SqlDictionary
if (ReleaseUpdateDB::changeTableId(
sqlDictionaryTable.tabId,
dictTable.id(),
dictTable.name()))
{
info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
}
}
fieldId = dictTable.fieldNext(0);
//For all fields in table
while (fieldId)
{
dictField = dictTable.fieldObject(fieldId);
if (dictField.isSql() && !dictField.isSystem())
{
//Finds fields in SqlDictionary by name and compares IDs
select sqlDictionaryField
where sqlDictionaryField.tabId == dictTable.id()
&& sqlDictionaryField.name == dictField.name()
&& sqlDictionaryField.fieldId != 0
&& sqlDictionaryField.fieldId != dictField.id();
if (sqlDictionaryField)
{
//Updates field ID in SqlDictionary
if (ReleaseUpdateDB::changeFieldId(
dictTable.id(),
sqlDictionaryField.fieldId,
dictField.id(),
dictTable.name(),
dictField.name()))
{
info(strFmt("Field %1 - ID changed (%2 -> %3)",
dictField.name(),
sqlDictionaryField.fieldId,
dictField.id()));
}
}
}
fieldId = dictTable.fieldNext(fieldId);
}
}
tableId = dictionary.tableNext(tableId);
}
ttscommit;
Hi, when you say "reimport the object (with id's)" does it means I should check this 2 checkbox?
ReplyDelete1)Import with ID values:
2)Enable data recovery for tables with chaged IDs