Export (0) Print
Expand All

Troubleshooting the Data upgrade cockpit

This section contains information about issues you may encounter when you use the Data upgrade cockpit.

Issue: The upgrade job doesn't start after clicking the Run or Trial Run buttons in the Data upgrade cockpit and all upgrade scripts remain in the Ready state for a long time.

NoteNote

You can update the upgrade status information manually if you click the Refresh button in the Data upgrade cockpit. To enable automatic updating of the upgrade status information, mark the Auto refresh enabled check box.


Possible cause #1: The database has been upgraded before (from Microsoft Axapta 2.5 to Microsoft Dynamics AX 3.0, for example) and a batch group named DataUpdate still exists but an associated job doesn't exist for this batch group.

Solution: Follow the steps below.

  1. Open the Batch group form (Administration > Setup > Batch groups).

  2. Select DataUpdate group.

  3. Click the Batch Servers tab.

  4. Verify that the current AOS is listed in the Selected servers list. If it is not listed there, move it there from the Remaining servers list and restart the upgrade.

Possible cause #2: The batch server defined for running the upgrade job is not running.

Solution: Open the Server configuration form (Administration > Setup > Server configuration). Verify that Is Batch Server is marked for the AOS instance that is running the upgrade.

Possible cause #3: The DataUpgrade batch group is not a selected group for the AOS instance that is running the ugprade.

Solution: Open the Server configuration form (Administration > Setup > Server configuration). On the Batch server groups tab, verify that DataUpdate batch group is listed in the Selected groups list.

If you experience data loss during the synchronization step of the Upgrade checklist, check the following things.

  1. Look for the Synchronization warning page and make sure there is no error.

  2. During synchronization, the AOT is synchronized with the database. If you have customizations which weren't properly upgraded, then these changes may not be the part of AOT and will get dropped during the synchronization process.

  3. During synchronization, if field IDs or table IDs have changed in Microsoft Dynamics AX 2009 from what they were in Microsoft Dynamics AX 3.0 or Microsoft Dynamics AX 4.0, data in those fields or tables will be lost. To avoid this, create an upgrade script to change the TableID as a part of the presynchronize process. See the white paper "How to Write Data Upgrade Scripts for Microsoft Dynamics AX 2009" for more information. Also, refer to the "Potential for dropped tables" section for information about a related issue.

  4. Changes to the database schema, such as defining user statistics on columns, may prevent objects from being synchronized with the AOT, leading to errors. Review the object causing the error and investigate by running the SQL statement (obtained from the Microsoft Dynamics AX Infolog or Windows Event Viewer) directly on the database using SQL Server Management Studio or the Oracle equivalent, such as SQLplus.

  5. You encounter an error mentioning DEL_<table>, which may indicate this table was not removed after a previous upgrade. Back up your Microsoft Dynamics AX 3.0 database, and then disable the Keep update objects 3.0 configuration key. When you clear these check boxes, any obsolete DEL_ objects in your source database will be deleted. Verify that your system runs properly before continuing the upgrade process.

  6. The following message is displayed: "Cannot execute a data definition language command on <table_name>," where <table_name> is a placeholder for the actual table where the issue occurs, and, in the AOS Application Event Log, a message is displayed stating that SQL was unable to rename an object because the object name already exists. Start open the AOT and locate the table where the issue occurs. If there are two tables with the same name, check to see if one exists in only the SYS layer and the other exists in SYS and another layer. If this is the case, right-click on the the table that exists in two different layers and choose Compare. Use the Compare tool to see if the name of the table has been changed. The table in question might have been renamed DEL_[table_name] in the new version. Make note of any modifications, and then delete the current layer object, the DEL_[table_name] will now be displayed in the AOT, and the table will no longer give a synchronization error.

  7. You get a message that says that duplicate field names exist but with different ID values. One way to fix this is to write upgrade scripts for the presynchronization process to fix the issue. Refer to the white paper "How to Write Data Upgrade Scripts for Microsoft Dynamics AX 2009".This issue may also be caused by exporting a table from one layer to another layer with object IDs. Be sure you do not export object IDs when exporting objects.

If you experience data loss during the Postsynchronize step of the Upgrade checklist, check the following things.

  1. Upgrade scripts can run only if the appropriate configuration keys are enabled. Find the tables or fields where the data was not upgraded and be sure that any configuration keys associated with those tables or fields are enabled.

  2. If an upgrade script fails, be sure to check the SQL statement causing the failures. You can find the SQL statements in the Microsoft Dynamics AX infolog or Windows Event Viewer.

If you are upgrading a large database, which may take several hours, you can improve performance by increasing the auto-refresh rate in the Auto-refresh rate time (sec) field to 1800 seconds or turn off the auto-refresh by clearing the checkbox, which will help improve performance.

You also can increase the number of batch threads defined for the upgrade batch server. Open the Server configuration form (Administration > Setup > Server configuration). On the Batch server schedule tab, enter a desired number in the Maximum batch threads column. Be sure to test the upgrade performance whenever you change the number of threads.

If you are using Oracle, be sure that you have the latest supported Oracle client with all of the required hotfixes installed.

For more information about ways to improve performance during data upgrade, read the Improving data upgrade performance topic.

You can detect performance issues within the upgrade job by doing the following.

  1. Sort through the Start time, Duration, and Upgrade job ID columns in the Data Upgrade Cockpit to identify tasks that are running an unusually long time.

  2. Click Cancel to prevent startup of all upgrade tasks with a status of Waiting. Tasks that have already been submitted to the batch server will continue to run. Because you abruptly stopped the process, invalid data might remain in the database, so you should restore from your backup before you attempt to perform the data upgrade again.

Issue: If you use SQL Server, and you are upgrading from Microsoft Dynamics AX 4.0 SP1 with an application build equal to or greater than 4.0.2500.61 or from Microsoft Dynamics AX 4.0 SP2, an error can occur during synchronization. In this case, the four tables listed below will fail and you will be unable to continue with the upgrade.

  • DimensionCollection

  • DimensionHierarchyCombination

  • DimensionSetCombinationDuplicate

  • DimensionSetCombinationTemp

Possible cause: The field IDs for the four tables listed above are different in Microsoft Dynamics AX 4.0 SP2 from Microsoft Dynamics AX 2009. However, the table IDs are the same in both releases, which causes the upgrade scripts to fail to start the upgrade code process to correct the field IDs. Because the field IDs are different in the database and the AOT, the fields are dropped and recreated during the synchronization step. The synchronization step fails because there are SQL indexes that reference the fields and prevent their removal.

Solution: Before you upgrade, you must execute the following SQL script on your Microsoft Dynamics AX 4.0 SP2 database.

--DIMENSIONCOLLECTION

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50002 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50005 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50007 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50008 AND TABLEID=2897;

--DIMENSIONHIERARCHYCOMBINATION

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50002 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50003 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50005 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50006 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=5 WHERE FIELDID=50009 AND TABLEID=2898;

--DIMENSIONSETCOMBINATIONDUP2899

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2899;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2899;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50003 AND TABLEID=2899;

--DIMENSIONSETCOMBINATIONTEMP

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50003 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50004 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=5 WHERE FIELDID=50005 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=6 WHERE FIELDID=50006 AND TABLEID=2900;

--PROVISIONALHIERARCHY

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2901;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2901;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50007 AND TABLEID=2901;

During synchronization, you might encounter an error that indicates that Microsoft Dynamics AX 2009 wants to drop and recreate a data table, which would cause the loss of all data in the table.

This issue may occur when you are re-implementing existing customizations instead of upgrading them. This generally occurs when a table ID changes, and causes the SQLDictionary table to not match what is in the AOT representation for the table. By dropping and recreating the table during the synchronization process, the SQLDictionary table is updated.

Listed below are two options for resolving this issue.

Method 1

The preferred way to resolve this issue is to use a static method of the ReleaseUpdateDB class. This method should be run during the presynchronization process to preserve the table data. For this example, the ReleaseUpdateDB41_Administration class will be used. The class used is dependent upon the module the table is related to.

  1. Open the AOT.

  2. Expand the Classes node.

  3. Locate the ReleaseUpdateDB41_Administration class.

  4. Double-click on this class to open the class in the editor.

  5. Click on the New button to create a new method.

  6. Type in the following code:

    void RepairCustomTable()
    {
        TableID     oldID;
        TableID     newID;
    ;
        oldID = 50008;
        newID = 50001;
        ReleaseUpdateDB::changeTableByName("COREEXCHANGETABLE",oldID,newID);
    }
    
  7. Save the code.

  8. Modify the initPreSyncJobs method to call the method created in steps 5–7. Highlight the initPreSyncJobs method in the left-most pane of the editor. The code is now displayed.

  9. Locate the section in the code beginning with "this.addStandardJob."

  10. Add the following line after the jobs listed in this section.

    this.addStandardJob(methodStr(ReleaseUpdateDB41_Administration, RepairCustomTable),'Backing up custom table');
    

11. Save the code. Now, when you run the upgrade process again, the custom code should load, and the table should be fixed.

Method 2

Another way to resolve this issue is to create a temporary table, populate it with the data from the table that has the issue, allow Microsoft Dynamics AX 2009 to drop and recreate the table, and then restore the data from the temporary table to the newly created table.

  1. Open the AOT.

  2. Expand the Classes node.

  3. Locate the ReleaseUpdateDB41_Administration class.

  4. Double-click on this class to open the class in the editor.

  5. Click on the New button to create a new method.

  6. Type in the following code:

    void backupCustomTable()
    {
        //back up a custom table so it may be dropped by the synch process
    
        SqlStatementExecutePermission   permission;
        Connection                      connection;
        Statement                       statement;
        SalesTable                      salesTable;
        SqlSystem                       sqlSystem = new SqlSystem();
    
        str ext;
        int tickCount;
        str sqlStatement;
        ;
    
        //create our connection
        connection  = new Connection();
        statement   = connection.createStatement();
    
        // coreexchangetable is our table with issues
        sqlStatement = strfmt(@"select * into COREEXCHANGETABLE_temp from COREEXCHANGETABLE");
    
        permission = new SqlStatementExecutePermission(sqlStatement);
        permission.assert();
        //BP deviation documented
        statement.executeUpdate(sqlStatement);
        CodeAccessPermission::revertAssert();
    }
    
    
  7. Save the code.

  8. Modify the initPreSyncJobs method to call the method created in steps 5–7. Highlight the initPreSyncJobs method in the left-most pane of the editor. The code is now displayed.

  9. Locate the section in the code beginning with "this.addStandardJob."

  10. Add the following line after the jobs listed in this section.

    this.addStandardJob(methodStr(ReleaseUpdateDB41_Administration, RepairCustomTable),'Backing up custom table');
    
  11. Save the code.

  12. The next step creates the method to restore the data after the synchronization process has recreated the table. Click on the New button to create a new method.

  13. Type the following code:

    void restoreCustomTable()
    {
        // back up a custom table so it may be dropped by the synch process 
        SqlStatementExecutePermission   permission;
        Connection                      connection;
        Statement                       statement;
        SalesTable                      salesTable;
        SqlSystem                       sqlSystem = new SqlSystem();
    
        str ext;
        int tickCount;
        str sqlStatement;
        ;
    
        //create our connection
        connection  = new Connection();
        statement   = connection.createStatement();
    
        // coreexchangetable is our table with issues
        sqlStatement = strfmt(@"insert into COREEXCHANGETABLE (EXCHANGEITEMSPERASSEMBLY,EXCHANGEITEMGROUP,EXCHANGEITEMNUMBER,ASSEMBLYITEMNUMBER,CORERETURNPERIOD,EXCHANGECONFIGID,ASSEMBLYCONFIGID,
    MODIFIEDDATE,MODIFIEDTIME,MODIFIEDBY,MODIFIEDTRANSACTIONID,CREATEDDATE,CREATEDTIME,CREATEDBY,CREATEDTRANSACTIONID,DATAAREAID,RECID) Select EXCHANGEITEMSPERASSEMBLY,EXCHANGEITEMGROUP,EXCHANGEITEMNUMBER,
    ASSEMBLYITEMNUMBER,CORERETURNPERIOD,EXCHANGECONFIGID,ASSEMBLYCONFIGID, MODIFIEDDATE,MODIFIEDTIME,MODIFIEDBY,MODIFIEDTRANSACTIONID,CREATEDDATE,CREATEDTIME,CREATEDBY,CREATEDTRANSACTIONID,DATAAREAID,RECID from COREEXCHANGETABLE_temp");
    
        permission = new SqlStatementExecutePermission(sqlStatement);
        permission.assert();
        //BP deviation documented
        statement.executeUpdate(sqlStatement);
        CodeAccessPermission::revertAssert();
    }
    
  14. Save the code.

  15. Modify the initPostSyncJobs method to call the method created in steps 12–14. Highlight the initPostSyncJobs method in the left-most pane of the editor. The code is now displayed.

  16. Locate the section in the code beginning with "this.addSharedJob."

  17. After this line of code, type the following.

    this.addSharedJob(methodStr(ReleaseUpdateDB41_Administration, restoreCustomTable),'Restore data into custom table');
    
  18. Save the code. At this point, the presynchronization should be able to run, and the custom method will back up the table. The synchronization process will prompt you about dropping the table, which can now happen safely, and postsynchronization should restore the data to the newly created table.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft