Wednesday, September 5, 2012

ID change in Dynamics AX data dictionary

The other day we upgraded to Cumulative Update 3 for Dynamics AX 2012. After that we got some problems in the SqlDictionary table - several table and field IDs did not much those in the AOT anymore.

One of our developers found this post, which contained a job fixing such issues. We had to correct the job a bit, otherwise it failed when trying to process Views or update field IDs that had been "swapped" during upgrade (e.g. before: fieldId1 = 6001, fieldId2 = 6002; after installing CU3: fieldId1 = 6002, fieldId2 = 6001).

This is the final version of the job. I know the change violates DRY principle, but for an ad-hoc job it is probably OK :)

static void fixTableAndFieldIds(Args _args)
{
    Dictionary dictionary = new Dictionary();
    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() && !dictTable.isView())
        {
            //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)
            {
                info(dictTable.name());
                //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.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("Pre-update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
 
            fieldId = dictTable.fieldNext(0);
 
            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);
 
                if (!dictField.isSystem())
                {
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId < 0;
 
                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -sqlDictionaryField.fieldId,
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Final update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -sqlDictionaryField.fieldId));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;
}

7 comments:

  1. I had the same issue recently but it was for updating a CU3 with the Feature pack.

    I used a similar script to update it.
    What I don't understand is why you are doing the pre update with negative ids and back again.

    I also did the skip for views too. But I wrote a script to give me a list of views to drop.
    ie.
    DROP VIEW myView

    The data sync will recreate the views in SQL.

    ReplyDelete
  2. Hi Munib

    I do pre-update to avoid a possible unique key violation.

    Say, you had these 2 records in the SqlDictionary table:

    TabId FieldId FieldName
    40001 60001 ItemId
    40001 60002 SalesId

    Then you upgraded, and in the AOT the ItemId and SalesId have their IDs swapped, so now ItemId field has ID = 60002, and SalesId has ID = 60001.

    Without a pre-update, the job will try to update the first record with the new ID, which is 60002, but 60002 is already there, owned by the second record. So an exception would be thrown. By setting the FieldIds to be negative in the first iteration I avoid that conflict, and the second pass will simply flip the sign and result in actual field IDs.

    ReplyDelete
    Replies
    1. Dear,

      I am using a similar script that runs after a data refresh (eg from production to live).

      What do you do in the following case:

      in sql dict you have (comes from production):

      60001 Field1
      60002 Field2

      In the TEST in AOT we have

      60001 Field2

      So the script should change the Id of Field2 from 60002 to 60001 but this will cause an error. So all entries in the SQl dictionary for non existing fields (in this case 60001 Field1 does not exist in the test app) should be deleted prior to running the script.

      I hope the problem is a bit clear :) .

      Thanks in advance.

      Andrew

      Delete
  3. Understood.
    I guess I haven't ran into a situation like that before. But that is good to know.

    ReplyDelete
  4. Good script, but I kept getting this error:
    Illegal data conversion from original field RETAILSTAFFTABLE.DEL_ID to RETAILSTAFFTABLE.DEL_RetailHcmWorker: Unable to convert data types to anything but character field type (0 to 49).
    I found that the ID's had a mismatch in the AOT/SqlDictionary after running this script.

    Going through the code, I found that these fields were mentioned in de SqlDictionary, were in the database, but isSql() returned false. This method should indicate if the field is in the database, but is was not correct in this case.
    I removed the 'issql()' validation and it works.

    Is there a reason why you check the isSql? If there is an incorrect sqldictionary entry, it should correct it.

    ReplyDelete
    Replies
    1. Hi Gerrit

      Thanks for the comment.

      We did see that issue with the isSql() method sometimes, and eventually had to remove it.

      I am not 100% sure why it was used, because my script was based on another script, published by another blogger.

      I have updated the post.

      Thank you

      Delete
  5. The isSQL() is called because in AX you can have fields with the property "SaveContents" to "No", which means they are not synced with the database, they are "virtual fields" which must be populated at record fecth time in the postLoad method on the table.

    ReplyDelete