Rebuilding SqlDictionary entries... (To avoid at all cost)

Whether or not this problem was the result of something I did, it seemed I just had to fix it. After the synchronization of Rollup 5 on one of our systems, we kept getting a database error for one of the tables. I just hate it when a table won't synchronize...

Since the error messages in AX tend to be so vague all the time, I still didn't know which table was failing. Next logical thing to do: fire up the SQL administration form in Administration/Periodic and run the Check/Synchronization. I quickly found the problem table was SalesParmUpdate and the info given to me was "Add table."

For a great read on data synchronization issues, take a look at this post from an old colleague of mine.

So I took a quick look in the SqlDictionary table and found absolutely no records for SalesParmUpdate (TableId: 1428)... The table existed in the AOT, it also existed in the database, but the records in SqlDictionary were missing. I decided to write a quick job that would recreate the records for a table. There's a few things to keep in mind... While normal UtcDateTime fields require two records (one for the DateTime and one for the Time Zone), the system DateTime fields don't need the Time Zone entry. Also, the RecId field uses an undocumented fieldType of 49.

I only have a few special cases in the code below (Enough for the SalesParmUpdate table) but you will probably need to tweak it a bit more to make it work with your table.

WARNING: PLEASE KNOW WHAT YOU ARE DOING BEFORE PLAYING AROUND WITH THE SQLDICTIONARY TABLE. ^^

static void rebuildSqlDictionary(Args _args)
{
    SqlDictionary   sqlDictionary;
    SqlDictionary   sqlDictionaryTZ;
    SysDictTable    sysDictTable;
    SysDictField    sysDictField;
    Set             tableFields;
    SetIterator     si;
    ;

    ttsbegin;

    sysDictTable = new SysDictTable(tablenum(SalesParmUpdate));

    tableFields = sysDictTable.fields();
    si = new SetIterator(tableFields);
    while (si.more())
    {
        sysDictField = si.value();

        sqlDictionary.clear();
        sqlDictionary.tabId = sysDictField.tableid();
        sqlDictionary.fieldId = fieldext2id(sysDictField.extendedFieldId());
        sqlDictionary.array = sysDictField.arrayIndex();
        sqlDictionary.name = strupr(sysDictField.name(DbBackend::Native, sysDictField.arrayIndex()));
        sqlDictionary.sqlName = sysDictField.name(DbBackend::Sql, sysDictField.arrayIndex());
        sqlDictionary.fieldType = sysDictField.baseType();

        switch (sqlDictionary.fieldType)
        {
            case Types::String :
                sqlDictionary.strSize = sysDictField.stringLen();
                break;
            case Types::UtcDateTime :
                if (!sysDictField.isSystem())
                {
                    sqlDictionaryTZ.clear();
                    sqlDictionaryTZ.data(sqlDictionary);
                    sqlDictionaryTZ.array = 2;
                    sqlDictionaryTZ.fieldType = Types::Integer;
                    sqlDictionaryTZ.sqlName += 'TZID';
                    sqlDictionaryTZ.doInsert();
                }
                break;
            case Types::Int64 :
                if (sysDictField.isSystem()) //RecId
                {
                    sqlDictionary.fieldType = 49;
                }
       }

        //sqlDictionary.flags = sysDictField.flags();
        sqlDictionary.doInsert();

        si.next();
    }

    ttscommit;
}