Sunday, April 19, 2015

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;
    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;
    while (tableId)
    {
        dictTable = new SysDictTable(tableId);
        setPrefix(dictTable.name());
        if ( dictTable.isSystemTable()
        ||   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();
        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()));
            }
        }
        ////////////
        // 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();
            }
        }
        ////////////
        //fieldId = dictTable.fieldNext(0);
        fieldCnt = dictTable.fieldCnt();
        fieldId = dictTable.fieldCnt2Id(fieldCnt);
        //For all fields in table
        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();
                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);
            fieldCnt--;
            fieldId = dictTable.fieldCnt2Id(fieldCnt);
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;

5 comments:

Trisha said...

Hello,
Thank you for posting this script, I have several IDs that are out of sync and need to run this to fix them. However, on the first field ID that the script tries to change, I am receiving this error:
Cannot edit a record in Table list (SqlDictionary). Table ID: 0, 0.
The record already exists.

I'm not sure what's going wrong, up until that point it has the correct field id selected and what it should be changed to. Any ideas?

Thanks!
Trisha

Claudio said...

Hi Denis,
I obtain same error of Trisha when the job try to change the first field with differences on sqlDictionary.

have you fixed the problem?

Thanks,

Anonymous said...

Might be late...I got around the above issue by removing the index I_65518RECID on the SQL Dictionary table in SSMS before running the script. AX2012 will recreate the index when you perform the database synchronization

Anonymous said...

even after removing the index on the sql dictionary table is SSMS still experiencing the same error.Kindly check.

Anonymous said...

I experienced the same issue. A workaround is to update the field Ids in two steps. On the first step the new Id may be : -dictField.id() [with negative value]
The second step will be the code as it is in this blog