Additional guidance to database upgrade scripts for D365 Finance and Operations. Part 2. Custom inventory dimensions data upgrade.

Introduction

About a year ago I posted an article about inventory dimensions data migration from Ax 2012 to D365 version. That post has general ideas regarding the inventory dimension data migration approach. This time I'm going to share some more consistent guidance. All my outcomes and ideas are based on my personal experience with custom dimensions data migration projects. The intercompany functionality is not revised in this post.

Before you go on reading this post, please read my other posts about the data upgrade process since some terms and definitions I use, were explained there.

Data requirements

The "InventDim" table is different in Ax 2012 and D365. In Ax 2012 you could add the custom inventory dimension as new fields to the "InventDim" table and change some InventDim* macros. In D365 you should map your custom dimension to the "InventDimension1" – "InventDimension10" based on this guide.  

In fact, during the data migration, you need to move values from Ax 2012 "InventDim" table fields to D365 "InventDimension1" – "InventDimension10"  "InventDim" table fields before the data upgrade logic is triggered. It should be done during the "PreSync" stage when the database has the table structure from Ax 2012 and D365 versions at that moment.

Also, the structure of the "WHSInventReserveTable" has been changed in D365 and those changes should be taken into consideration.

Functional requirements

During the data upgrade execution, the system should be configured properly, from an inventory management perspective. It means that:

1. The required configuration keys ("InventDimension1" – "InventDimension10") should be activated.
2. All reservation hierarchies should be set correctly.
3. All item dimension groups (Tracking, Storage, Product) should have proper setups.

If one of the mentioned requirements is not met during the date upgrade at the "PostSync" step, the custom inventory dimension migration will fail from a data consistency perspective.

Data upgrade

The "PreSync" stage

In order to meet the data requirements, we need to develop a script like this at the "PreSync" step:

[

  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);

}

It’s one of the possible options. The code is provided "as is" without any warranty. You can develop your own script in another way. It’s up to you. The key point is that the "InventDim" field values are to be moved at the "PreSync" stage properly.

The "PostSync" stage

At the "PostSync" stage when the database has a D365 table structure, the system performs the key steps from an inventory dimension data upgrade perspective.

There is a class "ReleaseUpdateDB73_WHS". It has the methods of the "WHSInventReserveTable" data upgrade. The key methods here are "populateParentInventDimIdOfWhsInventReserveMinor" and "populateParentInventDimIdOfWhsInventReserveMajor". Those methods call the "WHSInventReservePopulateParentInventDimId::populateForAllItems();" method. The method "populateForAllItems" populates the "WHSInventReserve" table based on the system setups that I mentioned in the functional requirements paragraph.

So, we need to develop an extension of this method and place the configuration keys activation code and update reservation hierarchies and dimension groups before the "next" method call. This is how we can meet the functional requirements before the data upgrade is triggered. In order to do it - add the extension to this method exactly since the data upgrade scripts can execute independently as I mentioned here.

The code changes can be like this:

/// <summary>

/// <c>WHSInventReservePopulateParentInventDimIdAXPUPG_Extension</c> class extension of <c>WHSInventReservePopulateParentInventDimId</c> class

/// </summary>

[ExtensionOf(classStr(WHSInventReservePopulateParentInventDimId))]

final class WHSInventReservePopulateParentInventDimIdAXPUPG_Extension

{

    /// <summary>

    /// Populates the <c>ParentInventDimId</c> field for all items.

    /// </summary>

    public static void populateForAllItems()

    {

        FieldId     fieldId2012; //you have to set your field identifier

        FieldId     fieldIdD365;

        ConfigurationKeySet keySet = new ConfigurationKeySet();

        SysGlobalCache      cache = appl.globalCache();

        boolean             isConfigChanged;

 

        void updateWHSReservationHierarchyElement()

        {

            WHSReservationHierarchyElement  hierarchyElement;

            hierarchyElement.skipDatabaseLog(true);

            ttsbegin;

            update_recordset hierarchyElement

            setting DimensionFieldId = fieldIdD365

                where hierarchyElement.DimensionFieldId == fieldId2012;

            ttscommit;

        }

 

        void updateEcoResTrackingDimensionGroupFldSetup()

        {

            EcoResTrackingDimensionGroupFldSetup     dimensionGroupFldSetup;

 

            dimensionGroupFldSetup.skipDatabaseLog(true);

 

            ttsbegin;

 

            update_recordset dimensionGroupFldSetup

            setting DimensionFieldId = fieldIdD365

                where dimensionGroupFldSetup.DimensionFieldId == fieldId2012;

 

            ttscommit;

        }

 

        if (//don’t forget to add a check 

            that the extension is calling during the data upgrade)

        {

            keySet.loadSystemSetup();

            if (

             !isConfigurationkeyEnabled(configurationKeyNum(InventDimension1)))

            {

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

                isConfigChanged  = true;

            }

 

            if (isConfigChanged)

            {

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

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

            }

 

            fieldIdBPHContainer = fieldNum(InventDim, InventDimension1);

       

            updateWHSReservationHierarchyElement();

            updateEcoResTrackingDimensionGroupFldSetup();

        }

 

        next populateForAllItems();

    }

}

It’s one of the possible options. The code id provided "as is" without any warranty. You can develop your own script in another way. It’s up to you. The key point is to activate the required inventory setups and configuration keys before the system can start the upgrade of the "WHSInventReserve" table.

Final steps

When the data upgrade is completed the "InventDimension1" - "InventDimension10" configuration keys could be disabled. You should check their status on the form "License configuration" ( System Administration/Setup/License configuration) under the configuration key "Trade". You need to enable the required keys manually if needed and align SQL warehouse procedures with the command:

https://YOUR_ENVIRONMENT_URL/?mi=SysClassRunner&cls=Tutorial_WHSSetup

Then you can open the "On-Hand" form, and add your custom dimensions to display and verify the outcomes. Also, it makes sense to do some functional tests. You can pick items via inventory journals, for instance.

Additional guidance to database upgrade scripts for D365 Finance and Operations. Part 1. General recommendations.

Introduction

As you may know, developing your own data upgrade scripts is something one can do. There are several posts related to this topic in my blog. Also, you can find documentation on this topic. This time, I would like to share my notes and experience with the technical aspects of development and execution of scripts. Some of the points are not described on the Microsoft Docs website.

Data upgrade methods name convention

All methods with data upgrade attributes must have unique names in the system. If you create 2 methods with the same names in the same module in different AXPUPGReleaseUpdate* classes the compiler will not show errors. You will get an error during the data upgrade execution:

Failed operation step '/DataUpgrade/PreSync/ExecuteScripts/ScheduleScripts' 

Cannot create a record in Release update scripts (ReleaseUpdateScripts).

Class ID: YOUR CLASS ID, METHOD NAME.

The record already exists.

   at Microsoft.Dynamics.Ax.MSIL.Interop.throwException(Int32 ExceptionValue, interpret* ip)

   at Microsoft.Dynamics.Ax.MSIL.cqlCursorIL.insert(IntPtr table)

   at Microsoft.Dynamics.Ax.Xpp.NativeCommonImplementation.Insert()

Also, if your methods have unique names, it’s easier to find them in the logs, if we also consider tracking.

Data upgrade script execution isolation

Technically each data upgrade method will be considered a separate batch task. It means that data upgrade scripts are executed separately and "don't know" about each other. If you didn't specify dependencies between data upgrade scripts you cannot be sure about their execution sequence. It is good to know this approach if you are going to develop complex data upgrade scenarios.

Using configuration keys

When the data upgrade is triggered the configuration keys in D365 Finance and Operations will be activated based on the "Enabled by Default" property. If there is a case when the configuration key is not enabled by default however it is needed to use the business logic covered by this key it is possible to enable the required key via code.

ConfigurationKeySet keySet = new ConfigurationKeySet();

SysGlobalCache      cache = appl.globalCache();

 

keySet.loadSystemSetup();

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

 

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

 

// Call SysSecurity::reload with the following parameters:

// _configurationChanged: true,

// _allowSynchronize: false,

// flushtable: true,

//_promptSynchronize: false,

//_syncRoleLicensesOnConfigurationChange: false

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

Based on the previous point regarding the data upgrade script isolation it is needed to enable the required configuration key in each data upgrade method for the tables and fields under the disabled by default configuration key in order to be on the safe side.

I had several cases when I needed to develop data upgrade scripts for the functionality with disabled configuration keys by default. When I enabled the configuration key in one method and did not enable the same key in another method, I got wrong results from a data consistency perspective.

General performance guidelines

You can find some points copied from the guide related to the Ax 2012 version and adjusted to the D365 version below. In fact, most of these recommendations also apply to D365. Since the performance is a critical part of the upgrade process I believe it’s a good idea to highlight these points once again.

In fact, most companies will perform this task over a weekend, so the entire upgrade process must be able to be completed within 48 hours. 

When you develop a new script, please try to apply it to your upgrade script:

• Use record set functions whenever possible. If the script performs inserts, updates, or deletes within a loop, you should consider changing the logic to use one of the set-based statements. If possible, use these set options to perform a single set-based operation.

• If your script runs delete_from or update_from on a large table where the delete() or update() methods of the target table have been overwritten, the bulk database operation will fall back to record-by-record processing. To prevent this, call the skipDataMethods(true) method to cause the update() and delete() methods to be skipped. Also, you can call the skipDatabaseLog(true) method to improve performance.
• If the business scenario cannot be written as insert_recordset, consider using the RecordInsertList class to batch multiple inserts to reduce network calls. This operation is not as fast as insert_recordset, but is faster than individual inserts in a loop.

• Break down your scripts into smaller pieces. For example, do not upgrade two independent tables in the same script even if there is a pattern in how the scripts work. This is because:

• Each script, by default, runs in one transaction (=one rollback segment) separately. If the segment becomes too large, the database server will start swapping memory to disk, and the script will slowly halt.
• Each script can be executed in parallel with other scripts as it was mentioned above.

• Take care when you sequence the scripts. For example, do not update data first and then delete it afterward.

• Be careful when calling normal business logic in your script. Normal business logic is not usually optimized for upgrade performance. For example, the same parameter record may be fetched for each record you need to upgrade. The parameter record is cached, but just calling the Find method takes an unacceptable amount of time. For example, the kernel overhead for each function call is about 5 ms. Usually, 10-15 ms will elapse before the Find method returns (when the record is cached). If there are a million rows, two hours will be spent getting the information you already have. The solution is to cache whatever is possible in local variables.

• If there is no business logic in the script, rewrite the script to issue a direct query to bulk update the data.

New requirement for developer cloud-hosted (OneBox) environments running version 10.0.36 or later

If you are going to upgrade your developer cloud-hosted (OneBox) environments to version 10.0.36 or later you should keep in mind that additional components should be installed manually in advance. The required component is an updated Microsoft Visual C++ redistributable package.

Otherwise, you will see the following error message during the upgrade process:

Error during AOS stop: Please upgrade to the latest Visual C++ redistributable package to continue with the installation. For more details, visit aka.ms/PreReqChanges [Log: C:\Temp\PU20\AXPlatformUpdate\RunbookWorkingFolder\AZH81-runbook\localhost\RetailHQConfiguration\2\Log\AutoStopAOS.log]

The step failed

P.S. All new required components are automatically installed in all newly deployed cloud-hosted environments.

Dynamics 365 Supply Chain Management new update policy

Currently, the vendor releases 7 updates for Dynamics 365 Finance & Supply Chain Management over the year and only two or three of them are major ones. 

Starting 2024, there will be significant changes to the release pattern and cadence. The changes go into effect with updates to some of the release milestones for 10.0.38. 

At the moment, based on the official documentation the key points of the new approach are: 

• The vendor will release four updates in December, March, June, and September. 
• The major updates will be released in March and September.
• Starting February 19, 2024, the maximum number of consecutive pauses of updates allowed will be reduced from three to one.
• With the release durations extended, the same minimum of two annual service updates is maintained

The following table illustrates allowed pauses by month based on your installed version until the transition is completed.

If you have any other questions, please go to One Version service updates FAQ to learn how these changes affect the release process.

Deploy development environment for Dynamics 365 Finance and Operations

When you deploy a new development environment there are 2 options:

• A cloud development environment in your Lifecycle Services (LCS) project
• VM that is running locally

All necessary documentation is available at the Microsoft resources. I would like to tell a few words about my experience with local VMs deployment.

1. You must be an administrator on the instance for developer access. To ensure your own credentials as an administrator on a local VM, run the "Admin user provisioning tool". On the local VM, you can find a link on the desktop. The tool should run as an administrator option (right-click the icon and then click Run as administrator). 

Note: If you see the Admin Provisioning Tool Error: "The value’s length for key ‘password’ exceeds it’s limit of ‘128’" it means that most probably you are using the VM with a virtual hard drive (VHD) that was released for versions 10.0.24 and later. In this case, you should follow the guidelines to ensure the process in order to resolve the issue.

Another possible reason is that your email is related to the inactive Azure Active Directory tenant or you just made a typo. 

2. If there is more than one developer using the local VMs and they are going to be linked in the same DevOps project I would recommend having unique environment names so that developer workspaces have unique names.

For this purpose, you need to go to the Control Panel and rename the VM:

The system will ask for a restart. I would postpone this action until the SQL Server instance will be renamed.

3. The next step is to rename the SQL Server instance. 

Note: My advice is: The SQL server instance should have the same name as the VM.

You need to run SQL Server Management Studio as an administrator option (right-click the icon and then click Run as administrator). Then you need to run the query:

--Run this with the updated names

sp_dropserver 'MININT-F36S5EH'; --Old Name

GO 

sp_addserver 'New VM Name', LOCAL; --New Name

GO

After that, the VM should be restarted. When it is running again, you should be able to connect the SQL Server instance with the new name via SQL  Server Management Studio. After that, you can run Visual Studio in order to establish the connection with your DevOps project and configure your workspace.

Run a runnable class (a job in terming of Ax 2009/2012) in Dynamics 365 Finance and Operation

Overview

In the previous versions of the system (AX 2009, AX 2012), you can create a new job in AOT: 

And run it via F5 button from AOT later:

In Dynamics 365 Finance and Operation there is another way. The option depends on the environment type.

Tier-1 environment

In Tier-1 environment, you have Visual Studio installed so you can create a runnable class (a job in terming of Ax 2009/2012) with the “main” method. Then you can run it via the link:

https://<D365URL> /?cmp=<YourCompanyName>&mi=SysClassRunner&cls=<YourRunnableClassName>

Tier-2 and higher environments

If your runnable class (a job in terming of Ax 2009/2012) was included in a binary package and the package is installed in Tier-2 environment, you can follow the same way - you can run it via the link:

https://<D365URL> /?cmp=<YourCompanyName>&mi=SysClassRunner&cls=<YourRunnableClassName>

If you need to run a runnable class (a job in terming of Ax 2009/2012) that was not installed in the environment you can use the feature "X++ scripts with zero downtime". This feature allows you to upload and run deployable packages that contain custom X++ scripts without having to go through Microsoft Dynamics Lifecycle Services (LCS) or suspending your system. Therefore, you can correct minor data inconsistencies without causing any disruptive downtime.

Of course, the feature requires a regular deployable package that can be created in Visual Studio. The deployable package must contain only one runnable X++ class. In other words, it must have one class that includes a “main” method. Then you need to upload and run a deployable package in the environment as described in the documentation.

In fact, you can create a package in a development machine (Tier-1) and add one class to this package as mentioned in the requirements. It is not necessary to do check-ins of the code to your Azure Dev Ops. You can create a deployable package in your development environment and use it with the feature.

From a technical perspective, the "Run custom X++ scripts with zero downtime" feature works as follows:

The system uses Assembly.LoadFrom API. This means the package is never deployed or installed in a traditional way. Once the execution is completed, there is no way to access this code again, when the AOS eventually restarts this assembly disappears from the memory too. No other AOSes will know, no other users can be influenced. Since it is loaded temporarily for the shortest duration possible no action is needed from ALM or uninstalling perspectives.

If you upload the package/model with the same name but with a new runnable class (a job in terming of Ax 2009/2012) inside, the system can show the following message:

If you ignore this message and run the new script, the previous runnable class will be executed.

It means that you should give unique names to your X++ scripts binary packages otherwise you might get unexpected results.

If you would like to test the feature in a Tier-1 environment you can enable the "AppConsistencyCustomScriptFlight" flag within the “SYSFLIGHTING” table.

Upgrade from AX 2012 to D365 Finance and Operations. Data upgrade in self-service environments.

Overview

When a successful upgrade test has been completed in a Tier-1 environment with customer data and developed data upgrade scripts you can start the data migration process in a Tier-2 machine. 

It has almost been 2 years since the old process via "backpack" files is no longer available. The only way to upgrade data is by using "Data Migration Toolkit for Dynamics 365" which uses SQL replication process. 

Note: An old name of the "Data Migration Toolkit for Dynamics 365" is "AX2012 Database Upgrade Toolkit for Dynamics365 Version".

The data upgrade process is described in Microsoft standard documentation. There is also a tech-talk session about the migration process. I would like to share my personal experience of the data upgrade in self-service Tier-2 environments below.

Key points before you start

• You should have free disc space in Ax 2012 database SQL server for distribution and snapshot folders. The disc space should be about 2 times more than Ax 2012 business database size.

• "Data Migration Toolkit for Dynamics 365" uses native SQL logins only. I would recommend to create a new SQL server login for this purpose.

• A new SQL login should have DB_Owner privilege in the source Ax 2012 database and access to the master database in the source SQL Server instance.

• Make sure that the replication feature is installed and enabled in the source Ax 2012 SQL Server instance. If the replication components aren't installed, follow the steps in Install SQL Server replication to install them.

• Don’t forget to enable and start SQL Server Agent on the source Ax 2012 database server.

• Download and install .NET Framework version 4.7.1 if it isn't already installed.

• You have to know the external IP address of the SQL Server machine. Use can use this website to help you. You should use IPv4 address field value.

• It is required to enable support for TLS 1.2 in your Ax 2012 SQL Server machine for Azure AD. To find some information on how to do it use this link. 

If you don’t enable TLS 1.2 support in advance, LCS authentication window might not work properly or not appear at all and then you see errors in the logs:

2023-06-25 06:47:27.202 -04:00 [Information] User Login started.

2023-06-25 06:49:38.538 -04:00 [Information] User Login failed.

2023-06-25 06:49:38.541 -04:00 [Error] AADSTS1002016: You are using TLS version 1.0, 1.1 and/or 3DES cipher which are deprecated to improve the security posture of Azure AD. 

Your TenantID is: g9370196-3d9a-9d85-a5e9-3604ec7ffbdd. 

Please refer to https://go.microsoft.com/fwlink/?linkid=2161187 and conduct needed actions to remediate the issue. For further questions, please contact your administrator.

Trace ID: g02f1232-caf4-479c-8c0e-0442059e5e01

Correlation ID: 52f25db8-e61a-4023-9e2a-c487f87e6c5d

Timestamp: 2023-06-25 10:49:37Z 2023-06-25 06:49:38.545 -04:00 

[Error] User login failed / not authorized.

• You need to make sure that TLS 1.2 is enabled by default and that the previous versions are disabled:

Tips to improve the process performance

• Since the "Data Migration Toolkit for Dynamics 365" is based on the SQL Server replication feature it moves data as it is from the source database. If you are short on time for data migration and would like to reduce the time span it is a good idea to perform cleanup operations in the source database.

• It is recommended that you start the replication during off hours when the system resources are at minimum usage (during off-peak time). 

• Also, you can use this article to improve replication performance on the Ax 2012 SQL server.

Customization Analysis Report (CAR) in D365 Finance and Operations

The Customization Analysis Report (CAR) is a tool that analyzes your customization and extension models and runs a predefined set of best practice rules. The report is one of the requirements of the solution certification process. 

Let’s imagine we have a model. It’s called the MYDEV model.

Normally we would use the following command to generate the Customization Analysis Report (CAR) in a development environment:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp -metadata=K:\AosService\PackagesLocalDirectory -all -model:MYDEV -module:MYDEV 

-car=K:\temp\CAReport.xlsx

If you would like to verify the particular object you can include additional parameters such as Class, Form, or another object type, for example:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp -metadata=K:\AosService\PackagesLocalDirectory -model:MYDEV -module:MYDEV 

-car=K:\temp\CAReport.xlsx Class:WHSDocumentRoutingFormMY_Extension

If your MYDEV model references a binary model (it can be a case when you use third-party or ISV models), then it is required to include the -PackagesRoot parameter, for example:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp 

-packagesroot=K:\AosService\PackagesLocalDirectory 

-metadata=K:\AosService\PackagesLocalDirectory 

-all -model:MYDEV -module:MYDEV -car=K:\temp\CAReport.xlsx

Database upgrade scripts for D365 Finance and Operations. Development of the data migration script via temporary table.

During the data migration from Ax 2012 to D365, the table might be renamed and there might be system setups e.g. electronic signatures for the table fields. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)

In this scenario, it would be good to store the table name and id, field names, and ids of the Ax 2012 table (“AATable”) in order to apply setups to the "BBTable" in D365 during the data migration. Using a temporary SQL table is a good choice to do so. 

You can find a code that can be used as an example in order to give you an idea, below. You can copy the queries in SQL Server Management Studio to see how it works.

First of all, it is needed to create a temporary SQL table via the SQL command at the "PreSync" stage:

private str getSQLCreateTmpSQLTable()

{

    str     tmpSQLTable = “TmpSQLTable”;

    str     sqlStatement;

 

    if (! this.isTablePresent(tmpSQLTable))

    {

        sqlStatement = strFmt(@"

                CREATE TABLE [dbo].%1(

                    [KEYFIELD] [nvarchar](10) NOT NULL,

                    [FIELD1] [nvarchar](60) NOT NULL,

                    [FIELD2] [int] NOT NULL,

                    [DATAAREAID] [nvarchar](4) NOT NULL,

                    [PARTITION] [bigint] NOT NULL,

                    CONSTRAINT [I_TMPSQLTABLEDATAAREA] PRIMARY KEY CLUSTERED

                    (

                        [DATAAREAID] ASC,

                        [PARTITION] ASC,

                        [KEYFIELD] ASC

                    )

                    WITH (PAD_INDEX = OFF,

                          STATISTICS_NORECOMPUTE = OFF,

                          IGNORE_DUP_KEY = OFF,

                          ALLOW_ROW_LOCKS = ON,

                          ALLOW_PAGE_LOCKS = ON)

                    ON [PRIMARY])

                ON [PRIMARY]",

                tmpSQLTable);

    }

 

    return sqlStatement;

}

/// <summary>

/// Defines, whether the table exists in the database.

/// </summary>

/// <param name = "_tableName">

/// The name of the table.

/// </param>

/// <returns>

/// True if the table exists, otherwise false.

/// </returns>

public boolean isTablePresent(TableName _tableName)

{

   str sqlQuery = strFmt(@"IF OBJECT_ID ('[dbo].[%1]', 'U') IS NULL

                         SELECT 0

                            ELSE

                         SELECT 1",

                         _tableName);

 

   str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);

 

   return str2Int(result);

}

NOTE: Enum fields are presented as number fields in Ax2012/D365. If you need to save enum field values you need to create an integer field.

Then populate the table with the data at the "PreSync" stage:

private str getSQLPopulateTmpSQLTable()

{

   TableName    tmpSQLTable = “TmpSQLTable”;

 

   str sqlStatement = strFmt(@"

            INSERT INTO [dbo].%1

                   (KEYFIELD,

                    FIELD1,

                    FIELD2,

                    DATAAREAID,

                    PARTITION)

            SELECT  [dbo].[INVENTITEMSAMPLING].[INVENTITEMSAMPLINGID],

                    [dbo].[INVENTITEMSAMPLING].[DESCRIPTION],

                    [dbo].[INVENTITEMSAMPLING].[TESTQTYSPECIFICATION],

                    [dbo].[INVENTITEMSAMPLING].[DATAAREAID],

                    [dbo].[INVENTITEMSAMPLING].[PARTITION]

            FROM [dbo].[INVENTITEMSAMPLING]

                    where [dbo].[INVENTITEMSAMPLING].[COMPLETEBLOCKING] = 1",

                            tmpSQLTable);

 

    return sqlStatement;

}

Now we can use the methods in the data upgrade method:

/// <summary>

/// Saving data to the temporary table.

/// </summary>

[UpgradeScriptDescription("@SYS113629"),

 UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),

 UpgradeScriptTable(tableStr(InventItemSampling), false, true, false, false)]

public void preSyncAQMInventItemSampling()

{

    str     sqlStatement;

       

    // Create the temporary upgrade table.

    sqlStatement = this.getSQLCreateTmpSQLTable();

    if (sqlStatement)

    {

        ReleaseUpdateDB::statementExeUpdate(sqlStatement);

    }

   

    // Populate the temporary table.

    sqlStatement = this. getSQLPopulateTmpSQLTable();

    if (sqlStatement)

    {

        ReleaseUpdateDB::statementExeUpdate(this.getSQLPopulateTmpSQLTable());

    }

}

The final step is to use the temporary table in order to manipulate data at the “PostSync” stage:

/// <summary>

/// Update the <c>InventItemSampling</c> table with the saved values.

/// </summary>

[UpgradeScriptDescription("@SYS113629"),

 UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),

 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),

 UpgradeScriptTable(tableStr(InventItemSampling), false, true, true, false)]

public void postSyncInventItemSampling()

{   

   str         sqlStatement;

   TableName   tmpSQLTable = “TmpSQLTable”;

       

   if (this.isTablePresent(tmpSQLTable))

   {

      sqlStatement = strFmt(@'UPDATE [dbo].[INVENTITEMSAMPLING]

                            SET [dbo].[INVENTITEMSAMPLING].[DESCRIPTION] = ''

                               from [dbo].[INVENTITEMSAMPLING] as iis

                            join [dbo].[%1] as upgTmp

                               ON  iis.INVENTITEMSAMPLINGID = upgTmp.KEYFIELD

                               and iis.DATAAREAID          = upgTmp.DATAAREAID

                               and iis.PARTITION           = upgTmp.PARTITION',

                             tmpSQLTable);

 

      ReleaseUpdateDB::statementExeUpdate(sqlStatement);

 

      sqlStatement = strFmt(@"DROP TABLE %1", tmpSQLTable);

 

      ReleaseUpdateDB::statementExeUpdate(sqlStatement);

   }   

}

I’ll be glad if you find this helpful.