DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 959258 - Last Review: August 3, 2011 - Revision: 2.0

This article applies to Microsoft Dynamics AX 4.0 for the Turkey (tr) region.

SYMPTOMS

When you upgrade from Microsoft Dynamics AX 3.0 SP4 or SP5 with the Turkish DIS layer to Microsoft Dynamics AX 4.0 SP1 with the Turkish GLS layer, you lose data in columns or in tables that have changed table IDs or field IDs.

For example, if the TBL1 table has a table ID of 16002 in Microsoft Dynamics AX 3.0, the table may have a table ID of 8002 after you upgrade to Microsoft Dynamics AX 4.0 SP1.

CAUSE

This problem occurs because the table was moved from the DIS layer to the GLS layer. Usually, pre-synchronization upgrade scripts handle this change. However, in the Turkish version Microsoft Dynamics AX, this change is not performed. Therefore, the data that refers to the TableId may be lost.

RESOLUTION

To work around this problem, follow these steps.

Note Make sure that you have a full backup of the application database.
  1. Run the AxDBUpgrade.exe tool, and then prepare the Microsoft Dynamics AX 4.0 SP1 application.
  2. Start the Application Object Server (AOS) service.
  3. Compile the application by using the first upgrade cockpit step.
  4. Run the pre-synchronization cockpit.
  5. Save the following code as the "CheckTableAndFieldDs.txt" file.
  6. Import and then run the class. This class corrects differences between IDs in the SQLDICTIONARY table and in the application object tree (AOT) in Microsoft Dynamics AX.

    ImportantMicrosoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
    class CHECKTableAndFieldIds
    {
    
    }
    
    public static server void checkFieldIds()
    {
        SysDictTable    sysDictTable;
        SysDictField    sysDictField;
        UtilElements    utilElements;
        TableId         tableId;
        TableId         oldTableId;
        FreeText        tableName;
        FreeText        fieldName;
        FieldId         fieldId;
        FieldId         oldFieldId;
        int             configKey;
        Set             setField = new Set(Types::String);
        ;
        while select utilElements
               where utilElements.recordType == UtilElementType::TableField
                  && utilElements.utilLevel  != UtilEntryLevel::sys
                  && utilElements.utilLevel  != UtilEntryLevel::syp
                  && utilElements.utilLevel  != UtilEntryLevel::dis
                  && utilElements.utilLevel  != UtilEntryLevel::dip
    
        {
            //tableId = utilElements.name);
            try
            {
            tableName   = strUpr(xUtilElements::parentElement(utilElements).name);
            tableId     = tableName2Id(tableName);
            if (tableId && !setField.in(tableName+"."+utilElements.name))
            {
                setField.add(tableName+"."+utilElements.name);
                sysDictTable = new SysDictTable(tableId);
                if (    !sysDictTable.isMap()
                    &&  !sysDictTable.isSystemTable()
                    &&  !sysDictTable.isView()
                    &&  !sysDictTable.isTmp())
                {
                    fieldName = strUpr(utilElements.name);
                    fieldId   = fieldName2Id(tableId,fieldName);
                    if (fieldId)
                    {
                        sysDictField = new SysDictField(tableId,fieldId);
                        if (sysDictField)
                        {
                                configKey = 0;
                                if (sysDictField.configurationKeyIdDefined())
                                {
                                    configKey = sysDictField.configurationKeyId();
                                }
    
                                if (!configKey || isConfigurationKeyEnabled(configKey))
                                {
                                    oldFieldId = CHECKTableAndFieldIds::getFieldIdDSQL(tableId,fieldName);
                                    if (oldFieldId && oldFieldId != fieldId)
                                    {
                                        info(strFmt("Field %1.%2 has different Ids.",tableName,fieldName));
                                        ttsBegIn;
                                        //ReleaseUpdateDB::changeFieldId(tableId,oldFieldId,fieldId,tableName,fieldName);
                                        ttsCommIt;
                                    }
                                    else if (!oldFieldId)
                                    {
                                        warning(strFmt("Field %1.%2 was not found in the dictionary.",tableName,fieldName));
                                        if (configKey)
                                            info(configurationkeyId2Name(configKey));
                                    }
                                }
                        }
                    }
                }
            }
            }
            catch
            {
            }
        }
    }
    
    public server static void checkTableIds()
    {
        SysDictTable    sysDictTable;
        UtilElements    utilElements_SYS;
        UtilElements    utilElements;
        TableId         tableId;
        TableId         oldTableId;
        FreeText        tableName;
        Set             setTable = new Set(Types::String);
        ;
        while select utilElements
               where utilElements.recordType == UtilElementType::Table
                  && utilElements.utilLevel  != UtilEntryLevel::sys
                  && utilElements.utilLevel  != UtilEntryLevel::syp
                  && utilElements.utilLevel  != UtilEntryLevel::dis
                  && utilElements.utilLevel  != UtilEntryLevel::dip
        {
            //check if it exists in SYS or SYP
            select firstOnly utilElements_SYS
                       where utilElements_SYS.recordType == UtilElementType::Table
                          && utilElements_SYS.name == utilElements.name
                          && (   utilElements_SYS.utilLevel  == UtilEntryLevel::sys
                              || utilElements_SYS.utilLevel  == UtilEntryLevel::syp);
    
            if (!utilElements_SYS && !setTable.in(utilElements.name))
            {
                setTable.add(utilElements.name);
                tableName = strUpr(utilElements.name);
                tableId   = tableName2Id(tableName);
                if (tableId)
                {
                    sysDictTable = new SysDictTable(tableId);
                    if (    !sysDictTable.isMap()
                        &&  !sysDictTable.isSystemTable()
                        &&  !sysDictTable.isView()
                        &&  !sysDictTable.isTmp())
                    {
    
                        oldTableId = CHECKTableAndFieldIds::getTableIdDSQL(tableName);
    
                        if (oldTableId && oldTableId != tableId)
                        {
                            info(strFmt("Table Name: %1, Table Id New: %2, Table Id Old: %3 has different IDs",
                                        strUpr(tableName),
                                        tableId,
                                        oldTableId));
                            ttsBegin;
                            //ReleaseUpdateDB::changeTableId(oldTableId,tableId,tableName);
                            ttsCommIt;
                        }
                    }
                }
            }
        }
    
    }
    
    
    public static server void checkTableAndFieldIDs()
    {
        ;
        CHECKTableAndFieldIds::checkTableIds();
        CHECKTableAndFieldIds::checkFieldIds();
        info("finished");
    }
    
    public server static FieldId getFieldIdDSQL(TableId _tableId, FieldName _fieldName)
    {
        SqlStatementExecutePermission   sqlStatementExecutePermission;
        str                             sqlStr;
        UserConnection                  userConnection;
        Statement                       statement;
        ResultSet                       resultSet;
        int                             retVal;
        ;
        sqlStr = "SELECT FIELDID FROM SQLDICTIONARY WHERE TABLEID=%1 AND NAME='%2' AND FIELDID<>0";
        sqlStr = strFmt(sqlStr,_tableId,_fieldName);
        new sqlStatementExecutePermission(sqlStr).assert();
        userConnection = new UserConnection();
        statement = userConnection.createStatement();
        resultSet = statement.executeQuery(sqlStr);
        if (resultSet.next())
        {
            retVal = resultSet.getInt(1);
        }
        else
        {
            retVal = 0;
        }
        return retVal;
    }
    
    
    public server static TableId getTableIdDSQL(TableName _tableName)
    {
        SqlStatementExecutePermission   sqlStatementExecutePermission;
        str                             sqlStr;
        UserConnection                  userConnection;
        Statement                       statement;
        ResultSet                       resultSet;
        int                             retVal;
        ;
        sqlStr = "SELECT TABLEID FROM SQLDICTIONARY WHERE NAME='%1' AND FIELDID=0";
        sqlStr = strFmt(sqlStr,_tableName);
        new sqlStatementExecutePermission(sqlStr).assert();
        userConnection = new UserConnection();
        statement = userConnection.createStatement();
        resultSet = statement.executeQuery(sqlStr);
        if (resultSet.next())
        {
            retVal = resultSet.getInt(1);
        }
        else
        {
            retVal = 0;
        }
        return retVal;
    }
    
    public static void main(Args _args)
    {
        ;
        if (Box::yesNo("Do you want to run the job?",DialogButton::No,"Confirmation","...") == DialogButton::Yes)
        {
            CHECKTableAndFieldIds::checkTableAndFieldIDs();
        }
    }
    
    
  7. Review messages that this class generates.
  8. If you must correct table IDs or field IDs, remove the "//" from the lines in the class that contain the following, as appropriate:
    • ReleaseUpdateDB::changeFieldId
    • ReleaseUpdateDB::changeTableId
    Then, run the class again.
  9. If you removed the "//" from the Release...::change calls in the class, run the synchronization, and then verify that you did not lose any data.

MORE INFORMATION

The upgrade scripts are as follows.

ImportantMicrosoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
class CHECKTableAndFieldIds
{

}

public static server void checkFieldIds()
{
    SysDictTable    sysDictTable;
    SysDictField    sysDictField;
    UtilElements    utilElements;
    TableId         tableId;
    TableId         oldTableId;
    FreeText        tableName;
    FreeText        fieldName;
    FieldId         fieldId;
    FieldId         oldFieldId;
    int             configKey;
    Set             setField = new Set(Types::String);
    ;
    while select utilElements
           where utilElements.recordType == UtilElementType::TableField
              && utilElements.utilLevel  != UtilEntryLevel::sys
              && utilElements.utilLevel  != UtilEntryLevel::syp
              && utilElements.utilLevel  != UtilEntryLevel::dis
              && utilElements.utilLevel  != UtilEntryLevel::dip

    {
        //tableId = utilElements.name);
        try
        {
        tableName   = strUpr(xUtilElements::parentElement(utilElements).name);
        tableId     = tableName2Id(tableName);
        if (tableId && !setField.in(tableName+"."+utilElements.name))
        {
            setField.add(tableName+"."+utilElements.name);
            sysDictTable = new SysDictTable(tableId);
            if (    !sysDictTable.isMap()
                &&  !sysDictTable.isSystemTable()
                &&  !sysDictTable.isView()
                &&  !sysDictTable.isTmp())
            {
                fieldName = strUpr(utilElements.name);
                fieldId   = fieldName2Id(tableId,fieldName);
                if (fieldId)
                {
                    sysDictField = new SysDictField(tableId,fieldId);
                    if (sysDictField)
                    {
                            configKey = 0;
                            if (sysDictField.configurationKeyIdDefined())
                            {
                                configKey = sysDictField.configurationKeyId();
                            }

                            if (!configKey || isConfigurationKeyEnabled(configKey))
                            {
                                oldFieldId = CHECKTableAndFieldIds::getFieldIdDSQL(tableId,fieldName);
                                if (oldFieldId && oldFieldId != fieldId)
                                {
                                    info(strFmt("Field %1.%2 has different Ids.",tableName,fieldName));
                                    ttsBegIn;
                                    //ReleaseUpdateDB::changeFieldId(tableId,oldFieldId,fieldId,tableName,fieldName);
                                    ttsCommIt;
                                }
                                else if (!oldFieldId)
                                {
                                    warning(strFmt("Field %1.%2 was not found in the dictionary.",tableName,fieldName));
                                    if (configKey)
                                        info(configurationkeyId2Name(configKey));
                                }
                            }
                    }
                }
            }
        }
        }
        catch
        {
        }
    }
}

public server static void checkTableIds()
{
    SysDictTable    sysDictTable;
    UtilElements    utilElements_SYS;
    UtilElements    utilElements;
    TableId         tableId;
    TableId         oldTableId;
    FreeText        tableName;
    Set             setTable = new Set(Types::String);
    ;
    while select utilElements
           where utilElements.recordType == UtilElementType::Table
              && utilElements.utilLevel  != UtilEntryLevel::sys
              && utilElements.utilLevel  != UtilEntryLevel::syp
              && utilElements.utilLevel  != UtilEntryLevel::dis
              && utilElements.utilLevel  != UtilEntryLevel::dip
    {
        //check if it exists in SYS or SYP
        select firstOnly utilElements_SYS
                   where utilElements_SYS.recordType == UtilElementType::Table
                      && utilElements_SYS.name == utilElements.name
                      && (   utilElements_SYS.utilLevel  == UtilEntryLevel::sys
                          || utilElements_SYS.utilLevel  == UtilEntryLevel::syp);

        if (!utilElements_SYS && !setTable.in(utilElements.name))
        {
            setTable.add(utilElements.name);
            tableName = strUpr(utilElements.name);
            tableId   = tableName2Id(tableName);
            if (tableId)
            {
                sysDictTable = new SysDictTable(tableId);
                if (    !sysDictTable.isMap()
                    &&  !sysDictTable.isSystemTable()
                    &&  !sysDictTable.isView()
                    &&  !sysDictTable.isTmp())
                {

                    oldTableId = CHECKTableAndFieldIds::getTableIdDSQL(tableName);

                    if (oldTableId && oldTableId != tableId)
                    {
                        info(strFmt("Table Name: %1, Table Id New: %2, Table Id Old: %3 has different IDs",
                                    strUpr(tableName),
                                    tableId,
                                    oldTableId));
                        ttsBegin;
                        //ReleaseUpdateDB::changeTableId(oldTableId,tableId,tableName);
                        ttsCommIt;
                    }
                }
            }
        }
    }

}


public static server void checkTableAndFieldIDs()
{
    ;
    CHECKTableAndFieldIds::checkTableIds();
    CHECKTableAndFieldIds::checkFieldIds();
    info("finished");
}

public server static FieldId getFieldIdDSQL(TableId _tableId, FieldName _fieldName)
{
    SqlStatementExecutePermission   sqlStatementExecutePermission;
    str                             sqlStr;
    UserConnection                  userConnection;
    Statement                       statement;
    ResultSet                       resultSet;
    int                             retVal;
    ;
    sqlStr = "SELECT FIELDID FROM SQLDICTIONARY WHERE TABLEID=%1 AND NAME='%2' AND FIELDID<>0";
    sqlStr = strFmt(sqlStr,_tableId,_fieldName);
    new sqlStatementExecutePermission(sqlStr).assert();
    userConnection = new UserConnection();
    statement = userConnection.createStatement();
    resultSet = statement.executeQuery(sqlStr);
    if (resultSet.next())
    {
        retVal = resultSet.getInt(1);
    }
    else
    {
        retVal = 0;
    }
    return retVal;
}


public server static TableId getTableIdDSQL(TableName _tableName)
{
    SqlStatementExecutePermission   sqlStatementExecutePermission;
    str                             sqlStr;
    UserConnection                  userConnection;
    Statement                       statement;
    ResultSet                       resultSet;
    int                             retVal;
    ;
    sqlStr = "SELECT TABLEID FROM SQLDICTIONARY WHERE NAME='%1' AND FIELDID=0";
    sqlStr = strFmt(sqlStr,_tableName);
    new sqlStatementExecutePermission(sqlStr).assert();
    userConnection = new UserConnection();
    statement = userConnection.createStatement();
    resultSet = statement.executeQuery(sqlStr);
    if (resultSet.next())
    {
        retVal = resultSet.getInt(1);
    }
    else
    {
        retVal = 0;
    }
    return retVal;
}

public static void main(Args _args)
{
    ;
    if (Box::yesNo("Do you want to run the job?",DialogButton::No,"Confirmation","...") == DialogButton::Yes)
    {
        CHECKTableAndFieldIds::checkTableAndFieldIDs();
    }
}

APPLIES TO
  • Microsoft Dynamics AX 4.0
Keywords: 
kbmbsupgrade kbexpertisebeginner kbtshoot kbmbsmigrate kbprb kbexpertiseinter KB959258
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support