Here's a script that can recreate SqlDictionary records in AX 2009 for any field of (nonsystem) table - in case such record is missing for some reason while the field exists in the database. I used this script during an upgrade to AX 2009 last year. Some fields in the standard application got the same IDs as fields added during customizations and they had different base types, of course, and it seemed impossible to fix the situation using standard means. The script might be of interest to those who wants to know the relations between the SqlDictionary and the AOT.
#macrolib.DictField
// This type is used instead of Types::Int64 for fields of type
// RecId/RefRecId/createdTransactionId/modifiedTransactionId
#define.RecIdBaseType (49)
// For nonsystem fields of type UtcDateTime an additional field is created
// that holds the actual time zone in which the value has been set
#define.TZIDsuffix ('_TZID')
SqlDictionary sqlDict;
SysdictType dictType;
DictTable dictTable;
DictField dictField;
ArrayIdx arrIdx;
Counter numOfSqlFields; // number of records created in SqlDictionary
fieldName fieldName;
fieldId fieldId;
tableId tableId = tablenum(TheTable2Fix); // TARGET
boolean processTableField(
DictField _dictField,
ArrayIdx _arrIdx,
boolean _isTzIdField = false
)
{
ArrayIdx dictArrIdx;
str infoName; // this field name is for messages only
FieldName sqlName;
boolean ret;
;
if (_isTzIdField)
{
if ( _dictField.baseType() != Types::UtcDateTime
|| _dictField.id() == fieldnum(Common, createdDateTime)
|| _dictField.id() == fieldnum(Common, modifiedDateTime)
)
{
throw error(Error::wrongUseOfFunction(funcname()));
}
dictArrIdx = _dictField.arraySize() + _arrIdx;
sqlName = _dictField.dateTimeTimeZoneRuleFieldName(_arrIdx - 1);
infoName = _dictField.name() + #TZIDsuffix;
}
else
{
dictArrIdx = _arrIdx;
sqlName = _dictField.name(DbBackend::Sql, _arrIdx);
infoName = _dictField.name();
}
select firstonly sqlDict
where sqlDict.tabId == _dictField.tableid()
&& sqlDict.fieldId == _dictField.id()
&& sqlDict.array == dictArrIdx
;
if (!sqlDict)
{
sqlDict.clear();
sqlDict.initValue();
sqlDict.tabId = _dictField.tableid();
sqlDict.fieldId = _dictField.id();
sqlDict.array = dictArrIdx;
sqlDict.name = strupr(_dictField.name(DbBackend::Native, _arrIdx));
sqlDict.sqlName = sqlName;
dictType = new SysDictType(_dictField.typeId());
if (_isTzIdField)
{
sqlDict.fieldType = Types::Integer;
}
else
if ( _dictField.id() == fieldnum(Common, RecId)
|| _dictField.id() == fieldnum(Common, createdTransactionId)
|| _dictField.id() == fieldnum(Common, modifiedTransactionId)
|| _dictField.typeId() == extendedtypenum(RecId)
|| _dictField.typeId() == extendedtypenum(RefRecId)
|| ( dictType
&& dictType.isExtending(extendedtypenum(RecId))
)
)
{
// This type is used instead of Types::Int64 for fields of type
// RecId/RefRecId/createdTransactionId/modifiedTransactionId
sqlDict.fieldType = #RecIdBaseType;
}
else
{
sqlDict.fieldType = _dictField.baseType();
}
sqlDict.strSize = _dictField.stringLen();
sqlDict.shadow = bitTest(_dictField.flags(), #DBF_SHADOW);
sqlDict.rightJustify = bitTest(_dictField.flags(), #DBF_RIGHT);
sqlDict.flags = sqlDict.shadow; // not _dictField.flags() at all!
sqlDict.nullable = _dictField.baseType() == Types::Container
|| _dictField.baseType() == Types::VarString
;
if (sqlDict.validateWrite())
{
sqlDict.insert();
ret = true;
info(strfmt(@"Created record for field %1.%2%3",
dictTable.name(), infoName,
_dictField.arraySize() > 1 ? strfmt(@"[%1]", _arrIdx) : ''));
// for all nonsystem UtcDateTime fields we also create a related TZID-field
if ( !_isTzIdField
&& _dictField.baseType() == Types::UtcDateTime
&& _dictField.id() != fieldnum(Common, createdDateTime)
&& _dictField.id() != fieldnum(Common, modifiedDateTime)
)
{
processTableField(_dictField, _arrIdx, true);
}
}
else
{
ret = checkFailed(strfmt(@"%1 record for %2.%3 was not created",
tablestr(SqlDictionary), dictTable.name(), infoName));
}
}
return ret;
}
;
dictTable = new DictTable(tableId);
if (!dictTable)
{
throw error(strfmt(@"Failed to create %1 for '%2' (%3)",
classstr(DictTable), tableid2name(tableId), tableId));
}
if (dictTable.isSystemTable())
{
throw error(strfmt(@"'%1' is a system table, no way...", dictTable.name()));
}
if (!dictTable.isSql())
{
throw error(strfmt(@"Table '%1' should not be in DB", dictTable.name()));
}
for (fieldId = dictTable.fieldNext(0); fieldId; fieldId = dictTable.fieldNext(fieldId))
{
dictField = dictTable.fieldObject(fieldId);
if (dictField && dictField.isSql())
{
fieldName = dictField.name();
for (arrIdx = 1; arrIdx <= dictField.arraySize(); arrIdx++)
{
numOfSqlFields++;
processTableField(dictField, arrIdx);
}
}
}
select firstonly sqlDict
where sqlDict.tabId == tableId
&& sqlDict.fieldId == 0
;
if (!sqlDict)
{
sqlDict.clear();
sqlDict.initValue();
sqlDict.tabId = tableId;
sqlDict.name = strupr(dictTable.name());
sqlDict.sqlName = dictTable.name(DbBackend::Sql);
sqlDict.strSize = numOfSqlFields; // for the table "header" - num of fields
sqlDict.flags = dictTable.isView(); // that's the way it is
sqlDict.insert();
info(strfmt(@"Created record for table %1", dictTable.name()));
}