AX 2012 - How to fix Data Dictionary ( SqlDictionary ) issue after importing a Model, ModelStore or during the TFS BUILD Process
Hi All
During the development phase, many tasks are made on the Data Dictionary.
Create tables and fields, rename or delete them.
So, often the Synchronization process failed.
To avoid this issue, you can create a process that fix the SqlDictionary Table.
During the time, I created many Jobs.
The main started from the Martin post with the following changes :
1. To avoid the duplicate key, I read the fields starting from the last one.
2. For each table, I delete first the fields from the SqlDictionary that no more exist on AOT
If you use the TFS Build, before the Synchronization you can run the above job.
The same during the Modelstore transportation scripts.
Here the code :
Dictionary dictionary = new Dictionary();
SysDictTable dictTable;
DictField dictField;
TableId tableId;
FieldId fieldId;
Counter fieldCnt;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryField;
SysDictTable dictTable;
DictField dictField;
TableId tableId;
FieldId fieldId;
Counter fieldCnt;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryField;
setPrefix("Update of data dictionary IDs");
tableId = dictionary.tableNext(0);
ttsbegin;
while (tableId)
{
dictTable = new SysDictTable(tableId);
setPrefix(dictTable.name());
{
dictTable = new SysDictTable(tableId);
setPrefix(dictTable.name());
if ( dictTable.isSystemTable()
|| dictTable.isView()
|| dicttable.isTempDb()
|| dictTable.isTmp()
)
{
tableId = dictionary.tableNext(tableId);
continue;
}
|| dictTable.isView()
|| dicttable.isTempDb()
|| dictTable.isTmp()
)
{
tableId = dictionary.tableNext(tableId);
continue;
}
///////////
//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();
//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()));
}
}
{
//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()));
}
}
////////////
// Delete Fields that no more exists in AOT
while select forUpdate sqlDictionaryTable
where sqlDictionaryTable.tabId == dictTable.id()
&& sqlDictionaryTable.fieldId
{
If ( ! dictTable.fieldName2Id( sqlDictionaryTable.name ) )
{
sqlDictionaryTable.delete();
}
}
while select forUpdate sqlDictionaryTable
where sqlDictionaryTable.tabId == dictTable.id()
&& sqlDictionaryTable.fieldId
{
If ( ! dictTable.fieldName2Id( sqlDictionaryTable.name ) )
{
sqlDictionaryTable.delete();
}
}
////////////
//fieldId = dictTable.fieldNext(0);
fieldCnt = dictTable.fieldCnt();
fieldId = dictTable.fieldCnt2Id(fieldCnt);
fieldCnt = dictTable.fieldCnt();
fieldId = dictTable.fieldCnt2Id(fieldCnt);
//For all fields in table
while (fieldCnt)
{
dictField = dictTable.fieldObject(fieldId);
while (fieldCnt)
{
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();
{
//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()));
}
}
}
{
//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);
fieldCnt--;
fieldCnt--;
fieldId = dictTable.fieldCnt2Id(fieldCnt);
}
}
tableId = dictionary.tableNext(tableId);
}
ttscommit;
ttscommit;
*This post is locked for comments