axStart: SQL Synchronize trouble, work around

[Blog bot]

Источник: http://axstart.spaces.live.com/Blog/...C0A0!206.entry
==============


A lot of time the ID of a table or field in the AOT changes (because it moved to a different layer).
When we want to synchronize the data dictionary, AX asks us if it is OK to drop the table. This will result in loss of data. The problem is that the SQLDictionary in the database has still the old ID (SQLDictionary ? see other blog on this site). So in case the dictionary ID change, we also have to correct the SQLDictionary. 
I hoop this small AX job will help you out to solve this. Be aware this is completely not MS best practice, but it works in all my projects J
Note: It is posible that you run into a scenario, that the script updates a record that violate the primary index on this table.

/* Beware that if you use this script, your database can get corrupted.  
** This will be the case if table ID & Field ID are used like a reference in the Data model  
** an example is Document management Module  
** The only purpose of this script is to avoid dropping of tables when code has changed from Layer  
*/  
static void updateSQLDictionary(Args _args)  
{  
    SQLDictionary   dictionary;  
    str             tableName;  
    int             AOTTableID, AOTFieldID;  
    TreeNode        treeNode;  
    boolean         doTable;  
    ;  
    ttsbegin;  
    while select forupdate dictionary order by tabid,fieldId where dictionary.flags == false  
    {  
        if(dictionary.fieldId == 0)  
        {  
            print dictionary.name;  
            treeNode     = TreeNode::findNode('\data dictionary\tables\'+dictionary.name);  
            if (treeNode)  
            {  
                tableName = dictionary.name;  
                AOTTableID = treeNode.applObjectId();  
   
                if(AOTTableID != dictionary.tabId)  
                {  
                    info(strfmt('risk at Table', dictionary.name));  
                    if(box::yesNo('Fix it?',DialogButton::No))  
                    {  
                        //repair;  
                        dictionary.tabId = AOTTableID;  
                        dictionary.update();  
                    }  
                }  
            }  
        }  
        else  
        {  
            //field  
            treeNode     = TreeNode::findNode('\data dictionary\tables\fields\'+dictionary.name);  
            if (treeNode)  
            {  
                 AOTFieldID = treeNode.applObjectId();  
                 if((AOTTableID != dictionary.tabId) || (AOTFieldID != dictionary.fieldId ))  
                 {  
                    info(strfmt('risk at Table %1 Field %2',tableName, dictionary.name));  
   
                    if(box::yesNo('Fix it? (prease also yes if Table ID is changed or you press yes in a other dimension field',DialogButton::No))  
                    {  
                        //repair, also repair when table ID is changed and field ID not.  
                        dictionary.tabId = AOTTableID;  
                        dictionary.fieldId = AOTFieldID;  
                        dictionary.update();  
                    }  
                }  
             }  
        }  
    }  
    if(box::okCancel('Operation completed, Last warning press Cancel to abort',DialogButton::Cancel))  
    {  
        ttscommit;  
    }  
    else  
    {  
        ttsabort;  
    }  
}

Источник: http://axstart.spaces.live.com/Blog/...C0A0!206.entry