On data dictionary synchronization errors：show which table is error

 

On data dictionary synchronization errors

 

Today, I'll describe some of the more common causes of Data Dictionary Synchronization failures, how to diagnose them and how to fix them. Most of this post should be applicable from Ax 2.5 all the way up to DAX 5.0.

 

Dictionary overview

 

Every object related to the SQL Database (tables, table fields, table indexes) is identified by 'name' in the Database, but identified by a 'unique id' in Dynamics Ax. This unique id is kept in code, in its respective layer file (*.aod). This unique id is a constant; SalesTable for example has had the table id '366' since forever. The object id found in the layer is matched with the Database object name through a table called 'SQLDictionary'. This table maps DAX table ids to SQL table names, and DAX field ids to SQL field names. This means that in theory and practice, a field could have a certain name under DAX, and a different name under the SQL Databse; this happens for example when the object name under the DAX AOT is longer than 30 characters.

 

During synchronization of the dictionnary under the DAX AOT, DAX looks through the SQLDictionary table and verifies that all tables and fields can be found, that they have the proper id and that the corresponding SQL object exists with the proper name and proper type.

 

Possible scenarios at synchronization 

1. An object (table or field) exists on the SQL Database, exists on a DAX layer and is properly mapped in the SQLDictionary table: DAX is properly aware of this object and nothing will occur at synchronization.
2. An object (table or field) exists on the SQL Database, but not on any DAX layer nor in the SQLDictionary table: DAX is not aware of this object and nothing will occur at synchronization.
3. An object (table or field) exists in the AOT and cannot be found in the SQLDictionary table: DAX will attempt to create the object in the SQL Database and insert a corresponding cross-reference record in SQLDictionary. Since DAX is not aware of the Database contents beyond the information contained in SQLDictionary a synchronization error will occur if an object of the same name already exists in the Database.
4. An object (table or field) exists in the AOT, can be found in the SQLDictionary table, but doesn't exist in the SQL Database: DAX will attempt to create the object in the SQL Database and update the corresponding cross-reference record in SQLDictionary.
5. A field exists in the AOT (suppose an Int64) and can be found in the SQLDictionary table (as an Int) as well as in the Database: A synchronization error will occur.
6. An object (table or field) exists in the AOT, can be found in the SQLDictionary table but with a different id, as well as in the Database (happens when a object was moved between layers and restoring a prior database): DAX wil attempt to recreate the object in the Database and will fail with a synchronization error. The original may be dropped.
7. An index exists in the AOT, but not in the SQL Database: DAX will attempt to introduce this new index to the table in the database. If the index is unique and records are seen as duplicates against it, a synchronization error will occur.

 

Diagnosing synchronization errors

 

Sometimes, DAX will fail to synchronize a table and give you an error message that does not identify the problematic table. This x++ job will help you determine where the cause of the error is:

 

public static void manualSync(Args _args)
{
    #define.unknown("UNKNOWN")
    #define.tableIdMax(65536)
    int i = 1;
    ;

    while (i < #tableIdMax)
    {
        if (tableid2name(i) != #unknown)
        {
            print(tableid2name(i));

            try
            {
                if (!appl.dbSynchronize(i, false, true))
                    throw Exception::Error;
            }
            catch
            {
                error(tableid2name(i));
            }
        }

        i++;
    }
}

 

When ids don't match...

 

Generally you will have table id and field id mismatches upon re-attaching a database when objects where moved across different layers. The reason for this is that of the 65 536 available tables ids, ids [0, 10 000] are reserved for SYS, ids [20 000, 30 000] are reserved for BUS, ids [40 000, 50 000] are reserved for CUS, etc; with no overlap between the grops of ids (I may be slightly off, this is from memory).

 

While migrating a customer from Ax 2.5 to Ax 4.0, they revised they're customization strategy to bring mods onto the CUS layer, when originally they were on the USR layer. We didn't want to spend hours importing data using the data import-export tool, so I ran jobs to fix the SQLDictionary table (you would also need to fix the data in any RefTableId field). This was the basic idea:

 

(NEVER RUN THIS CODE IN A PRODUCTION ENVIRONMENT)

 

/*
 *  Warning, this code does not work 'as-is', it is only given as example
 *  This will completely destroy an environment if run 'as-is'!
 */
public static void fixSQLDictionary(Args _args)
{
    SQLDictionary sqlDictionary;

    #Define.CUSLAYERSTART(40000)
    #Define.USRLAYEREND  (60000)

    boolean validate()
    {
        if(sqlDictionary.FieldId == 0 && sqlDictionary.Name != tableid2name(sqlDictionary.TabId))
        {
            return false;
        }
        else if(sqlDictionary.FieldId != 0
            && sqlDictionary.Name != fieldid2name(sqlDictionary.TabId, sqlDictionary.FieldId))
        {
            return false;
        }
        return true;
    }

    ;

    try
    {
        ttsbegin;

        //FIX CUSTOM TABLES AND THEIR FIELDS
        while select forupdate sqlDictionary
            where sqlDictionary.TabId >= #CUSLAYERSTART
                && sqlDictionary.TabId < #USRLAYEREND
        {
            sqlDictionary.TabId = tablename2id(sqlDictionary.Name);

            if (sqlDictionary.orig().FieldId != 0)
                sqlDictionary.FieldId = fieldname2id(sqlDictionary.TabId, sqlDictionary.Name);

            if (!validate())
            {
                //TODO: Need to deal with DEL_* objects and objects with names > 30 chars
                continue;
            }

            //TODO: Uncomment
            //sqlDictionary.doUpdate();
        }

        //FIX CUSTOM FIELDS ON STD TABLES
        while select forupdate sqlDictionary
            where (sqlDictionary.FieldId >= #CUSLAYERSTART
                    && sqlDictionary.FieldId < #USRLAYEREND)
                && (sqlDictionary.Tabid < #CUSLAYERSTART)
        {
            sqlDictionary.FieldId = fieldname2id(sqlDictionary.TabId, sqlDictionary.Name);
            if(!validate())
            {
                //TODO: Need to deal with DEL_* objects and objects with names > 30 chars
                continue;
            }
           
            //TODO: Uncomment
            //sqlDictionary.doUpdate();
        }

        ttscommit;
    }
    catch
    {
        throw error("Aborted");
    }
}