MSDyn365FO. Collation conflict during data upgrade from AX 2012

AX 2012 has well-known inconsistency in columns collation that has been reported before and now it is causing issues during data upgrade to latest version (8.0) of Dynamics 365 for Finance and Operations.

Data upgrade fails on step 9. postSync for data upgrade. From RELEASEUPDATESCRIPTSERRORLOG table you can get actual error: “[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot resolve the collation conflict between  “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.” It fails on the next x++ update statement in ReleaseUpdateDB80_TaxGTE_IN.updateTaxMeasureType() method:

update_recordset crosscompany taxMeasureType
    setting ClassNumber = sysModelElement.AxId
       where !taxMeasureType.ClassNumber
       join AxId from sysModelElement
       	where sysModelElement.ElementType == UtilElementType::Class
           && sysModelElement.Name == taxMeasureType.ClassName;

It was introduced in 8.0 update. Here it uses sysModelElement.Name and taxMeasureType.ClassName fields that have different collation.

Now we have several options to fix this:

• Skip this method if you don’t use IN localization.
• Convert source DB to D365 server collation.
• Change collation of Name column in ModelElement table.

I already knew that we cannot simple use

ALTER DATABASE AxDB COLLATE SQL_Latin1_General_CP1_CI_AS;   <span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

So, I looked at bacpac option proposed by Lane Swenka. We can export DB to a bacpac, edit collation there and import it again. But after I’ve got several thousand errors during the export I decided to switch to other options. I decided not to touch ModelElement because I hope that there is a reason behind its collation, so I went easiest rout for me – skip the code I don’t need.

I did not find nice and easy way to skip this method because everything is done via reflection and it’s hard to skip something using extension, but there is always a dirty way!

Quick extension forces D365 to run different method one more time that updates nothing instead of method I don’t want:

[ExtensionOf(classStr(SysDictClass))]
public final class SysDictClass_FUS_Extension
{
    public static anytype invokeObjectMethod(Object _object, IdentifierName _methodName, boolean _tryBaseClass)
    {
        if (_methodName == "updateTaxMeasureType")
        {
            _methodName = "updateTaxDocumentExtensionIN";
        }

        return next invokeObjectMethod(_object, _methodName, _tryBaseClass);
    }
}

That’s all. After this small hack update goes nice and smoothly.

Solution proposed here is not a real solution, but a hack. It is ok for me because I’m 100% sure that I don’t need this data to be upgraded. I would strongly recommend converting DB to SQL server collation or raise this with MS in case you hit this to get real solution that would be supported.

Happy hacking!