Dynamics 365 Business Central: are you doing cloud data migration? Please sanitize your database before doing it.

I’ve decided to write this quick post after having spent some hours on supporting a partner in order to discover problems with a Dynamics 365 Business Central online instance.

The partner has moved customer’s data from a Dynamics 365 Business Central v14 C/AL database (onpremise) to Dynamics 365 Business Central online. Migration was successful, but then he had a strange error on code when working on some records. The error was raised by simple GET operations on Code fields, like:

Customer.Get(CustomerNo)

where CustomerNo contains the No. field (primary key) of the record to retrieve.

What happened here?

After investigating, we discover that the data migration migrates data from the original database with Code fields containing non-printing characters, like for example CRLF or TABS. These characters on Code fields were supported on old versions of the ERP, but the current platform does not support them (and a GET on them throws an error).

If you want to perform a clean data migration to the online service, before doing that it’s absolutely recommended to open the Business Central Administration Shell and then to execute the Invoke-NAVSanitizeField Powershell cmdlet.

This cmdlet trims all the unsupported characters from fields of type Code in the tenant database and returns an object containing the number of rows and values modified, the tables impacted and a list of potentially ignored records.

Executing it on the entire database is super easy. Just launch the following command:

$result = Invoke-NAVSanitizeField -ServerInstance YOURSERVERINSTANCE -Tenant YOURTENANT

Here $result is an object containing the results of the sanitize field operation.

As an example, here the execution on our test database for this case:

Result was this:

A Customer record with a CRLF field value on the No. was found in the original database (I think this was mainy caused by copy/paste from Excel cells. Remember that if you copy an entire cell, it has also CRLF characters in the value).

After data sanitization, problem solved (so please remember it). 🙂

10 Comments

    1. Thanks.
      Curious on a couple of points.
      What was the strange error received from the get()? was it a standard no record exist for CustomerNo?
      What does nav do with these characters after the upgrade? Can we see the Crlf character on the customer record in BC?

      Like

      1. The GET fails (record not found).
        The sanitization simply removes all strange characters from Code fields in the database. These chars are removed from the field, so not more present in BC online data.

        Like

  1. An important detail to keep in mind is that this is done in a transaction to avoid causing data corruption. Tables being sanitized are locked, so the system should not be in use while cleaning it up.

    Very glad to see this feature being useful 🙂

    Liked by 1 person

  2. Question arizen: did it clean up also all related tables and non-PK fields on them? Like Customer Ledger Entry’s Customer No.?
    Or only PK fields like Customer/Item/etc?

    Like

  3. Question arizen: did it clean up also all related tables and non-PK fields on them? Like Customer Ledger Entry’s Customer No.?
    Or only PK fields like Customer/Item/etc?

    Like

Leave a Reply to ST Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.