RSS

ID change in Dynamics AX data dictionary

10 Nov

If a table ID or field ID is changed in Dynamics AX, data are lost during synchronization, because the table with the old ID (and containing data) is dropped at the beginning and then a new table is created with the same structure but a different ID. The same is valid analogically for table fields.

The data loss can be prevented in several ways (e.g. by data export and reimport), but one solution is really simple and painless. Realize how the situation looks in AX after the ID change (e.g. after installation of a layer with different IDs) but before the database synchronization:

  1. Data are still in the database
  2. Table metadata in AOT contain the new ID
  3. Data in SqlDictionary table contain the old ID

So it’s not necessary to export data or to put old IDs down, all information is still in the system. You just have to secure that synchronization is not run prematurely.

You can identify all changed IDs by comparing values in AOT with values in SqlDictionary. And the update of SqlDictionary to the new ID can prevent the regeneration of database objects during synchronization.

I use several scripts for this purpose, this is the simplest one:

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())
    {
        //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;

If my memory isn’t failing me, this script works in AX4 – AX2012, but Axapta 3.0 doesn’t have changeTableId() a changeFieldId() methods in ReleaseUpdateDB and you have to implement them by yourself.

It is often forgotten that object IDs exist also in business data in database – one example is the ID of a table to which a document is attached. Ignoring this issue can affect database integrity, which is again felt by users as a data loss. One of possible solutions is to use myDataReferenceSearcher – although it actually doesn’t fix the found references, it helps you to write necessary data upgrade scripts.

Advertisements
 
Leave a comment

Posted by on November 10, 2013 in Installation and Configuration

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: