Database upgrade scripts for D365 Finance and Operations. Troubleshooting data upgrade script errors in a development environment.

Overview

When you run a data upgrade process it can end with errors and it would be nice to know the reasons. I’d like to share a useful link that I have been using for my investigations. In addition, I would like to share my personal experience as well.

If the data upgrade runbook fails, you can see the following message on your screen:

If you use the query on the AXDB database to determine which step the upgrade servicing is on, you can get the following results, for instance:

In this case, it would be good to know the reason for the error. There are different options for this purpose.

Review the runbook logs

Logs should be located in a sub-folder under the deployable package. Drill into the logs folder to find the logs for the runbook step you're on, and review errors. In our example for the third step, it can be:

C:\....\DataUpgradePackage\RunbookWorkingFolder\MajorVersionDataUpgrade-runbook\localhost\AOSService\3\Log

When you open “dataupgrade_err” file you will find a system error message and this information can be used for the investigation. Some of the issues are well known and there is a solution for them.

The most common reason is various types of synchronization errors. There is an example below:

In order to fix such errors it is needed to populate data by properly upgrading scripts during the data upgrade process. 

Also, you may see an error like:

Cannot create a record in Release update scripts (ReleaseUpdateScripts). Class ID: 23613, “The method name”.

The cause of this error – there are 2 class methods with the same name among “ReleaseUpdateDB” classes.

If you see an error like:

Cannot execute the required database operation. The SQL database has issued an error.

Usually, the cause is a wrong data upgrade script and the log file contains enough details to find the reason.

View details about a script error via SQL query

To upgrade scripts run in X++ using a batch process that the runbook installer starts. In Application Explorer in Visual Studio, some classes you can see prefixed with ReleaseUpdate. Let’s say an upgrade script fails during the runbook process. In that case, you can learn more about the reason for the error by opening Microsoft SQL Server Management Studio and running the following code to query ReleaseUpdateScriptsErrorLog.

SELECT * FROM [AXDB].[dbo].[RELEASEUPDATESCRIPTSERRORLOG]

View details about a script error via Event Viewer

If you face an error during database sync or in Sync and Post Sync scripts, you can go to Windows Event Viewer and find the errors in the appropriate section. 

Database sync and final database sync details are logged in section Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX DatabaseSynchronize.

Sync and Post Sync script details are logged in section Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX DataUpgrade.

Skip failed scripts

You can skip all scripts that have failed a number of times and move to the next viable scripts. This functionality helps with the troubleshooting process. This process is 100% manual, so you're less likely to skip the scripts unintentionally.

In ReleaseUpdateConfiguration table, there is a field ScriptRetryCount. The value in this field controls how many times the runbook process will rerun scripts before it ignores them. When the runbook is running, the system updates ReleaseUpdateScriptsErrorLog.ErrorCount field every time a specific script fails. A new row is created for each script.

In DataUpgrade package folder, under ..\AosServices\Scripts\, (In our example C:\...\DataUpgradePackage\AOSService\Scripts) there is a script IgnoreBlockingScripts.ps1. Run this script from an Administrator Windows PowerShell window to skip all scripts where ScriptRetryCount=ErrorCount. Then rerun the failed runbook step so those scripts will be ignored. ReleaseUpdateScriptsErrorLog. The ignored field will also be set for each script that is skipped. Therefore, you can easily identify skipped scripts later.

Let’s have a look at the example. Data upgrade script «*******_Invent» has an error. Data upgrade process failed. If we query ReleaseUpdateScriptsErrorLog table:

SELECT * FROM [AXDB].[dbo].[RELEASEUPDATESCRIPTSERRORLOG]

We can get a result:

If we rerun the runbook and query ReleaseUpdateScriptsErrorLog table one more time, we will get the result:

In order to ignore «******_Invent» script we need to run IgnoreBlockingScripts.ps1 script from .\AosServices\Scripts folder (In our example C:\...\DataUpgradePackage\AOSService\Scripts)

NOTE: The script must be executed with administrative permissions; otherwise, it won’t work.

Before running the script, $webroot variable should be specified. Its value should be equal to the path of AOSService website. On the screenshot below the option for the Tier-1 machine is presented:

When all necessary changes are done, the script can be executed. If the script was executed correctly, ReleaseUpdateScriptsErrorLog.The ignored field will be set to “1”.

After that, the failed runbook step can be rerun via -rerun step command.

Database upgrade scripts for D365 Finance and Operations. Sequence of upgrading steps.

Every script must be defined in its scope and order within a step. There are the following attributes to deal with it (ordered by execution order during the step):

1. ReleaseUpdateScriptType::StartScript - Tells Upgrade Framework to schedule the script as a start script at the step. The script will run once for each company (DataArea) in the system at the start of the step.

2. ReleaseUpdateScriptType::PartitionStartScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system at the start of the step. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

3. ReleaseUpdateScriptType::SharedScript - Tells Upgrade Framework to schedule the script as a shared script. The script will run only once. It can be used when “SaveDataPerCompany” and “SaveDataPerPartition” properties on the table accessed by the script are both set to “No”.

4. ReleaseUpdateScriptType::StandardScript - Tells Upgrade Framework to schedule the script as a standard script. The script will run once for each company (DataArea) in the system. It can be used when “SaveDataPerCompany” property on the table being accessed by the script is set to “Yes”.

5. ReleaseUpdateScriptType::PartitionScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

6. ReleaseUpdateScriptType::FinalScript - Tells Upgrade Framework to schedule the script as a final script at the step. The script will run once for each company (DataArea) in the system at the end of the step.

7. ReleaseUpdateScriptType::PartitionFinalScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system at the end of the step. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

Database upgrade scripts for D365 Finance and Operations. Upgrade steps overview.

The standard application code has many standard data migration scripts. Their main purpose is to migrate data for standard tables. 

The process of data migration has different steps which include Pre-req, Pre-sync, Database sync, Post-sync, Final database sync, stopping and starting various services and many more.

Below you will find a short description of the key steps.

• Pre-req - At this step, the upgrade framework prepares the source database for an upgrade. This step patches the SQL dictionary, applies SQL sequences instead of system sequences, modifies user info and system variables, synchronizes system tables of the database, and performs an additive synchronization of new tables. 

• Pre-sync - At this step, the upgrade framework executes Pre-synchronize upgrade scripts to make metadata changes before step “synchronize tables”. 

For example, pre-synchronize scripts map the database table or fields on the source Microsoft Dynamics AX system to the database table or fields in the target system. This table-to-table and column-to-column mapping prepares the source-to-target upgrade model for data copy from source to target. Other kinds of pre-synchronize scripts disable unique indexes in preparation for data upgrades and avoid duplicating key errors. This action is undone during Post-synchronize step.

In other words, at this stage, there is the full set of metadata of both versions. The database contains tables from Ax 2012 and their data as well as the full set of new D365 tables. During this step, you can copy data from Ax 2012 tables and fields to D365 if the table or field was renamed in the version. When this step is completed, the database data should be whole from a D365 application perspective. Otherwise, you will see synchronization errors at the next stage.

In case it is needed to write scripts to move data from specific Ax 2012 fields to the appropriate D365 field and tables it can be done via direct SQL command because Ax 2012 fields do not exist in D365 version from a D365 code application perspective.

• Database sync - At this step, the upgrade framework executes the database synchronization with  the current application code. When this step is completed all tables that do not exist in D365  application will be deleted and their data will be lost.

• Post-sync –  At this step, the upgrade framework executes post-synchronization upgrade scripts that contain the bulk of the data upgrade scripts. The database contains the fields and tables from D365 version only. So all data from Ax 2012 was lost at this step. 

Usually, company-specific business data is upgraded in post-synchronization upgrade scripts. Post-synchronization also reverses the metadata changes (disables unique indexes) that were made during the pre-synchronize step. During post-synchronization, upgrade scripts run to update existing data when required and populate newly created tables and columns. Running upgrade scripts and any inter-dependencies are controlled by the upgrade framework. Upgrade scripts are implemented for any major version change.

At this stage, it is possible to write scripts to manipulate migrated data in D365 tables via code or SQL commands.

• FinalDBSync – This step does the final database synchronization that synchronizes all remaining objects in the database.

D365 Finance and Operations disable the flight feature in a Tier-1 environment.

When we recently were updating one of our Tier-1 environments to 10.0.31 version we faced an issue with WHS mobile app.

The mobile app reverted to the login screen after trying to go to a specific menu. We tried the following options in order to resolve the issue:

• Delete all user sessions from Warehouse management > Inquiries and reports > Mobile device logs > Work user sessions.
• Delete and recreate the mobile app user configured/used for your tests (Warehouse management > Setup > Worker)

After an investigation we realized that the issue was rooting in Issue 704649 - Warehouse mobile app gives error "The size of the XML request exceeds the maximum valued allowed". This code changes controls with the flight WHSMobileAppXMLSizeValidationFlight.

So we decided to disable this flight by using the SYSFLIGHTING table. The procedure is nicely described in an old post I found here, I will adjust the procedure to our case.

For this purpose it is needed to add a record with the field Enabled = 1 for the kill switch for the flight, meaning for WHSMobileAppXMLSizeValidationFlight_KillSwitch.

Below specific steps to follow to enable the kill switch for the flight:

1. Add a record with this Insert statement for SYSFLIGHTING table, but please replace the appropriate values:

INSERT INTO SYSFLIGHTING VALUES ('FlightName', 1, 12719367, Partition, RecID, 1)

Note: After replacing 'FlightName' with the actual flight name, the values are/should be:

- 1 stands for Enabled

- 12719367 is the Flight service ID

- Partition = partition ID from your environment which can be obtained by querying (select) for any record. Every record will have a partition id which must be copied and used here.

- RecID = same ID as partition(*) (If there is no other record in SYSFLIGHTING table, then it can be one. Or you can can find one by executing "SELECT max(recid)+1 from SysFlighting")

- 1 is for the RecVersion

- actually, the SQL query could also be much simpler, like this

INSERT INTO SYSFLIGHTING ([FLIGHTNAME],[ENABLED],[FLIGHTSERVICEID]) VALUES ('WHSMobileAppXMLSizeValidationFlight_KillSwitch', 1, 12719367)

2 Verify that C:\AOSService\webroot\web.config has the correct DataAccess.FlightingServiceCatalogID. You should find a line with this key and a value of 12719367. If not, update accordingly the file and save it.

Notes:

Having web.config file in the environment is a clear indication that key DataAccess.FlightingServiceCatalogID should have value 12719367 and this value should be used also in the SQL statement from above for the FLIGHTSERVICEID field value.

If web.config file is not present in the environment, but AXService.config file is present (see next note), it is a clear indication that environment is like an On-Premise deployment (which might be the case also for dev boxes or environments that are not Microsoft managed, but still on Microsoft cloud). In this scenario, see the next note with important differences.

Notes: (not having web.config file in the environment):

Verify that C:\ProgramData\SF\AOS_182\Fabric\work\Applications\AXSFType_App641\AXSF.Code.1.0.20200715180202\AXService.config has the correct DataAccess.FlightingServiceCatalogID. You should find a line with this key and a value of 0. If not, update accordingly the file and save it.

The AXService.config file path might be different (accordingly to your installation).

Having AXService.config file in the environment is a clear indication that key DataAccess.FlightingServiceCatalogID should have value 0 and this value should be used also in the SQL statement from above for the FLIGHTSERVICEID field value.

So, the SQL query should be like this:

INSERT INTO SYSFLIGHTING ([FLIGHTNAME],[ENABLED],[FLIGHTSERVICEID]) VALUES ('WHSMobileAppXMLSizeValidationFlight_KillSwitch', 1, 0)

3. Restart IIS and batch service

P.S. The issue was reported to Microsoft and it can be solved in 10.0.33 version.

D365 Finance and Operations. How to get the RecId sequence of the table via X++.

In D365 RecID there is a SQL system sequence now. SQL itself maintains the IDs. I found a post on this topic. 

Using SQL commands described there, I created an X++ method in order to get the sequence name of the table.

It can be useful if you develop a data upgrade script and it is needed to align the sequence when data is moved ‘as is’ to the target D365 table.

public str getSequenceProcedureName(TableName   _tableName)

{

   str         result;

   str         ret;

   TableId     tableId;

 

   if (this.isTablePresent(_tableName))

   {

       str sqlQuery = strFmt(@"

                SELECT ID from TABLEIDTABLE

                    JOIN sys.sequences ON sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)

                    WHERE TABLEIDTABLE.NAME = '%1'",

                    _tableName);

 

       result  =  ReleaseUpdateDB::statementExeQuery(sqlQuery);

       if (result)

       {

          tableId = str2Int(result);

 

          if (tableId)

          {

             ret = strFmt('SEQ_%1',tableId);

          }

       }

    }

 

    return ret;

}

D365 Finance and Operations. How to get the numeric value of the extensible enum via X++.

There are 2 enum types in the system from an extensibility perspective – extensible and non-extensible. The difference is briefly explained here. There is a short recap below:

In D365FO enums that are not extensible keep the numeric value of each element in the metadata. 

However, extensible enums do not use metadata to store their numeric values. Instead, these values are stored in the database and are assigned dynamically by the database synchronization process.

The ENUMIDTABLE table stores the base enum ids, while the ENUMVALUETABLE table stores the numeric values assigned to every element of the base enum. While the ENUMIDTABLE table contains records for every base enum in the system, ENUMVALUETABLE is only populated for extensible enums.

In case we need to get the numeric value of the extensible enum the following method can be useful:

public Integer getEnumValue(str     _enumName,

                            str     _enumValueName)

{

   Integer     ret;

 

   if (_enumName && _enumValueName)

   {

      str sqlQuery = strFmt(@"SELECT ENUMVALUE from ENUMVALUETABLE

                            join ENUMIDTABLE on enumid = id

                            and ENUMIDTABLE.NAME    = '%1'

                            and ENUMVALUETABLE.NAME = '%2'",

                            _enumName,

                            _enumValueName);

 

       str result   =  ReleaseUpdateDB::statementExeQuery(sqlQuery);

       if (result)

       {

           ret = str2Int(result);

       }

   }

      

   return ret;

}

Data upgrade from Ax 2012 to Dynamics 365 Finance and Operations. Migrate inventory dimensions.

Recently one of the customers asked me if a data migration from Ax 2012 to the latest finance and operations version is possible. The Ax 2012 solution has additional inventory dimensions that migrated to the D365 from a code perspective. While investigating the issue I faced some issues and I would like to share my experience regarding inventory dimensions data upgrade. I hope it will save your time.

The documentation regarding the data upgrade process is pretty clear and there is a separate page related to the troubleshooting upgrade scripts experience.

During the data migration in development environment, I faced the same error as mentioned on the troubleshooting page:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.INVENTDIM' and the index name 'I_698SHA1HASHIDX'. The duplicate key value is (5637144576, USMF, B92BAF2504FD67FC15A56F2DFACE09127715B54B). The statement has been terminated. CREATE UNIQUE INDEX I_698SHA1HASHIDX ON DBO.INVENTDIM(PARTITION,DATAAREAID,SHA1HASHHEX) WITH (MAXDOP = 1)

It was unexpected since a special inventory dimension data migration script was developed. When I run the recommended query to verify the script, this is what appears:

SELECT PARTITION, DATAAREAID, SHA1HASHHEX, COUNT(*)

FROM INVENTDIM

GROUP BY PARTITION, DATAAREAID, SHA1HASHHEX

HAVING COUNT(*) > 1

The query returned many records. In fact, it means that the SHA1HASHHEX field values were not generated as unique. In my opinion, there can be two reasons except those described in the documentation: The values of InventDimension* fields are not taken into consideration with the ReleaseUpdateDB72_Invent::updateSHA1HashHexInInventDim method or the InventDimension* fields are empty.

In my case, the reason was that the appropriate configuration keys InventDimension* are disabled in the system by default. Even if I violated the system and copied the data from Ax 2012 inventory dimensions fields to the appropriate InventDimension* fields via direct SQL, the script still looks like this:

[

  UpgradeScriptDescription("Script description"),

  UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

  UpgradeScriptType(ReleaseUpdateScriptType::StandardScript),

  UpgradeScriptTable(tableStr(InventDim), false, true, true, false)

]

public void updateInventDimensionField()

{

    FieldName     field2012Name  = 'YOURDIMENSIONFIELD';

  FieldName     field365Name   = 'INVENTDIMENSION1';

  SysDictTable  inventDimDT    = SysDictTable::newTableId(tableNum(InventDim));

  TableName     inventDimName  = inventDimDT.name(DbBackend::Sql);

  str sqlStatement = strFmt(@"

                UPDATE [dbo].[%1]

                SET [dbo].[%1].[%2] = [dbo].[%1].[%3]

                WHERE [dbo].[%1].[%3] <> ''",

                inventDimName,

                field365Name,

                field2012Name);

   

  ReleaseUpdateDB::statementExeUpdate(sqlStatement);

}

The system did not use its values with the generate hash values method and the above-mentioned synchronization issue appeared.

In order to fix this issue, the required configuration keys should be activated during the data upgrade. In order to perform it, the following method can be used:

[

  UpgradeScriptDescription("Enable inventory dimension configuration keys"),

  UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

  UpgradeScriptType(ReleaseUpdateScriptType::StartScript),

    Microsoft.Dynamics.BusinessPlatform.SharedTypes.InternalUseOnlyAttribute

]

public void enablePreSyncInventDimensionConfigurationKeys()

{

  ConfigurationKeySet keySet = new ConfigurationKeySet();

  SysGlobalCache      cache = appl.globalCache();

  keySet.loadSystemSetup();

  keySet.enabled(configurationKeyNum(InventDimension1), true);

  keySet.enabled(configurationKeyNum(InventDimension2), true);

  SysDictConfigurationKey::save(keySet.pack());

  SysSecurity::reload(true, true, true, false, false);

}

When the above- mentioned method is used and a data transfer between Ax 2012 and D365 InventDim fields is completed (It should be done at the PreSync step, otherwise the Ax 2012 InventDim custom fields will be deleted at the DbSync step), the ReleaseUpdateDB72_Invent::updateSHA1HashHexInInventDim method should generate hash values correctly and the synchronization issue should be resolved.

Additionally, I recommend updating other tables that contain InventDim field ids from Ax2012 version since InventDimension* fields in D365 have other field id values. I believe every system may have various setups and customizations. Therefore, from my point of view, it make sense to develop scripts for updating setups and the field id values of the InventDim table in the tables listed below:

• WHSReservationHierarchyElement
• EcoResStorageDimensionGroupFldSetup
• EcoResTrackingDimensionGroupFldSetup   
• EcoResProductDimensionGroupFldSetup 

The correct values in the InventDim table and in the tables listed above, as well as the enabled configuration keys, should help to avoid problems with data WHS* tables within the upgrade process.

Finally, if you see any of these errors after the data migration:

DECLARE @AVAILPHYSICAL numeric(32, 6) DECLARE @AVAILORDERED numeric(32, 6); EXECUTE WHSOnHandWithInventDim @DATAAREAID = N'USMF', @PARTITION = 5637144576, @ITEMID = N'ITEMIDXXX', @LEVEL = 6, @UPPERLEVELOFMINIMUMCALCULATION = 0, @INVENTSITEID = N'1', @INVENTSITEIDLEVEL = 1, @INVENTLOCATIONID = N'16', @INVENTLOCATIONIDLEVEL = 2, @INVENTSTATUSIDLEVEL = 3, @INVENTBATCHIDLEVEL = 4, @INVENTDIMENSION1 = N'DIMENSIONVALUE', @INVENTDIMENSION1LEVEL = 5, @WMSLOCATIONID = N'12', @WMSLOCATIONIDLEVEL = 6, @AVAILPHYSICAL = @AVAILPHYSICAL output, @AVAILORDERED = @AVAILORDERED output ; SELECT @AVAILPHYSICAL as [AVAILPHYSICAL], @AVAILORDERED as [AVAILORDERED];

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]@INVENTDIMENSION1 is not a parameter for procedure WHSOnHandWithInventDim.

It means the WHSOnHandWithInventDim and WHSOnHandWithDelta procedures were not adjusted to the current dimension configuration setups. It could happen if at the final database synchronization step during the data migration process the required configuration keys were not enabled. In order to fix this error, you can run the code via a job:

WHSOnHandSPHelper::syncDBStoredProcedures();

It should align the mentioned stored procedures to the current activated InventDimension* configuration keys and the errors should no longer be.

D365 Finance and Operations. Get path for the menu item via code.

In one of my previous posts, I have shared an idea of how a new Dynamics 365 SCM and Finance API can be used to get metadata of Dynamics 365 AOT elements. 

Learning a path to a menu item from a user interface perspective could come in handy.

So I have created the method that I would like to share:

public static str getMenuItemPath(str _menuItemName)

{

   MenuFunction mf = new MenuFunction(_menuItemName, MenuItemType::Display);

   str          menuLabel;

   str          ret;

   boolean iterateMenus(SysDictMenu _menu)

   {

      SysMenuEnumerator       menuEnum = _menu.getEnumerator();

      SysDictMenu             subMenu;

      boolean                 found;

      while (menuEnum.moveNext())

      {

         subMenu = menuEnum.current();

         if (subMenu.isMenu() || subMenu.isMenuReference())

         {

            found = iterateMenus(subMenu);

            if (found) // If found, just climb back up the stack

            {

               menuLabel = strFmt('%1/%2', subMenu.label(), menuLabel);

               return found;

            }

         }

         else

         {

            if (subMenu.isMenuItem()

             && subMenu.isValid()

             && subMenu.isVisible()

             && subMenu.menuItem().name() == mf.name()

             && subMenu.menuItem().type() == mf.type())

            {

               menuLabel = subMenu.label();

               return true;

            }

         }

      }

      return false;

   }

   If (iterateMenus(SysDictMenu::newMainMenu()))

   {

      ret = strfmt('%1', menuLabel);

   }

   return ret;

}

You can find a couple of examples of using the method presented above, below :

1. info(strFmt('%1', Class::getMenuItemPath('SalesTableListPage')));

It will return the result: Accounts receivable/Orders/All sales orders

2. info(strFmt('%1', Class::getMenuItemPath(menuItemDisplayStr(InventLocations))));

It will return the result: Inventory management/Setup/Inventory breakdown/Warehouses