Starting with NAV 2015 there is an easier way to migrate data between old and new structures of NAV tables.
From msdn excerpt, the upgrade codeunits role is to provide “the logic for migrating existing data in the business data table from the old format to the new format after schema synchronization”.
When we operate a change to the structure of a table the system will detect there are discrepancies between what we just saved through the development environment and what is current in SQL Server.
When we save the object we can switch between three options available for “Synchronize schema”. By default the value is “Now – with validation”. If we choose “Later” the object is saved but no schema synchronization is performed. If we choose “Force”, the object is saved and the new structure is operated at the SQL Server level with potential data loss.
If we choose “Now – with validation”, the system will attempt to synchronize schema and because there are structural changes it will check the existence of an upgrade codeunit that can perform the changes without data loss. If such an upgrade unit does not exist we get the error above.
To see the upgrade codeunit in action, I will:
- Create a table
- Populate it
- Change its structure:
- change the ID# of a field
- change the data type of a field
- insert new field in the freed ID#
- Confirm schema sync error
- Create upgrade codeunit
- Attempt to save the object with Schema Synchronization “Now – with validation”
- Start the upgrade process
- Check upgrade output
Let’s create a table, populate it with some data and attempt to change its structure.
1. Create a table similar to the one below:
2. Create a codeunit to populate the newly created table:
3. Let’s change the structure of the table as following:
- ID 6 Status (Option) -> ID 20 Status (Option) [Update1]
- ID 4 Field_3 (Integer) -> ID 4 Field_3(Code10) [Update2]
- ID 6 Description (Text30) (new field) [Update3]
Check the current status of the table pre-upgrade in Management Studio
4. If trying to save the object with Schema synchronization “Now – with validation” after [Update1] and [Update2] we get this error:
If we try to save the object after [Update1],[Update2] and [Update3]
we get this error:
Notice how the system reports only the last update for field 6, I assume because the Update1 is not committed.
5. Let’s now create the upgrade codeunit:
Start by setting up the Subtype property of the codeunit to “Upgrade”.
The codeunit consists of two functions:
- A function that will back up the data from our table into a temporary table. [My temporary table is pretty much a copy of the original table. But if you’re not changing a lot of fields in your process, I suggest having in your temp table just the fields in the primary key and the fields that are affected.]. Set the FunctionType property of this function to TableSyncSetup.
A function that will populate the new structure with the data from the temporary backup table … something like a “forced” schema synchronization with data restore from a backup. Set the FunctionType property of this function to UpgradePerCompany
6. Save the object (compile option for schema synchronization Now – with validation). No errors should be encountered at this point if the upgrade codeunit was created.
7. Start the upgrade from the development environment (Tools-Data Upgrade – Start …) or if you prefer powershell, run in Administration Shell: Start-NAVDataUpgrade [InstanceName]
8. Check the content of the table:
I used upgrade codeunits in day-to-day tasks(recently changing the data type of a field from integer to code), and in upgrade projects when existent fields have been relocated to different IDs.
When the Data Upgrade processes is suspended you can identify potential errors in your upgrade codeunit by running a powershell command: Get-NAVDataUpgrade [InstanceName] -ErrorOnly
But it’s not possible to step through the codeunit with the debugger … or, to be more precise, I couldn’t find how.
Sample code here.
