Re-align field ID’s

While all effort should be made to prevent ID’s in your DAX environment to get out of aligned, if they do then the below script might be of assistance. This script is designed to re-align field ID’s only and should be run after the table Id’s re-alignment script, if necessary.

static void ReAlignFieldIds(Args _args)
{
Dictionary dictionary = new Dictionary();
SysDictTable dictTable;
DictField dictField;
TableId tableId;
FieldId fieldId;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryField;
SqlDictionary sqlDictionaryFieldNew;

int64 icount;
SysOperationProgress op;

boolean bPass = false;
boolean bPassTest;

setPrefix(“Update of data dictionary IDs”);

select count(RecId) from sqlDictionaryTable
where sqlDictionaryTable.fieldId == 0;

tableId = dictionary.tableNext(0);

op = new SysOperationProgress();
op.setTotal(sqlDictionaryTable.RecId);

while (tableId)
{
dictTable = new SysDictTable(tableId);
setPrefix(dictTable.name());

op.incCount();
op.setText(dictTable.name());

if (!dictTable.isSystemTable())
{
fieldId = dictTable.fieldNext(0);
bPassTest = false;
bPass = false;

//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)
{
info(strFmt(“Field %1 – ID changed (%2 -> %3)”,
dictField.name(),
dictField.id(),
sqlDictionaryField.fieldId));
if(bPass == true)
{
//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(),
dictField.id(),
sqlDictionaryField.fieldId));
}
}
else
{
select maxOf(FieldId) from sqlDictionaryFieldNew
where sqlDictionaryFieldNew.tabId == dictTable.id()
&& sqlDictionaryFieldNew.fieldId < 60999
&& sqlDictionaryFieldNew.fieldId >= 60000;

if(sqlDictionaryFieldNew.fieldId < 60000)
sqlDictionaryFieldNew.fieldId = 60000;

ReleaseUpdateDB::changeFieldId(
dictTable.id(),
sqlDictionaryField.fieldId,
sqlDictionaryFieldNew.fieldId + 1,
dictTable.name(),
dictField.name());

bPassTest = true;
}
}
}
fieldId = dictTable.fieldNext(fieldId);

if(!fieldId && bPassTest)
{
fieldId = dictTable.fieldNext(0);
bPass = true;
bPassTest = false;
}
}
}
tableId = dictionary.tableNext(tableId);
}
}

Advertisements

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