Day-to-day NAV: Upgrade codeunits

Standard

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:

  1. Create a table
  2. Populate it
  3. Change its structure:
    • change the ID# of a field
    • change the data type of a field
    • insert new field in the freed ID#
  4. Confirm schema sync error
  5. Create upgrade codeunit
  6. Attempt to save the object with Schema Synchronization “Now – with validation”
  7. Start the upgrade process
  8. 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:

1-table

2. Create a codeunit to populate the newly created table:

codeunit-populate

   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

struct-pre-upgrade

4. If trying to save the object with Schema synchronization “Now – with validation” after [Update1] and [Update2] we get this error:

error1

If we try to save the object after [Update1],[Update2] and [Update3]

we get this error:

error2

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.

tablesyncsetupfnA 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

restoredata

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:

afterupgrade

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.

Advertisements

NAV 2016 – Applying a new NAV Cumulative Update using Powershell cmdlets

Standard

I’ve done a few C/AL code merging for Dynamics NAV in the past, and while I don’t mind working with BeyondCompare for a few hours every now and then, the task is repetitive and prone to errors so I was searching for a cleaner and faster approach. A few days ago my boss told me about (NAV) Powershell commands and that I should give them a try. Why not?

I liked the idea of running a few Powershell commands that do the bulk of the job, from merging to compiling an entire database with minimal assistance on my side. The less “personal touch” the better. Less time spent on redundant tasks lead to less mistakes, and more time for development and trying new technologies.

And this is where I am now. The process still requires (minimal) developer’s interaction – for updating standard objects (VersionList propery) touched by AddOns objects, comparing a few conflict files and of course, running the commands. Albeit all this, the time I recorded to apply a cumulative update is at the minimum halved. Is there room for further improvement? Definitely, with a few more experiments under the belt and testing, the process can further be refined into a single script run, cutting 4-6 hours task to a few minutes.

The steps below are based on a recent task I worked on: to apply  standard NAV 2016 CU11 on top of a NAV 2016 CU5 database + my employer AddOn.

Pre-requisites:

  • A previous CU demo database (the last one you applied) – let’s call it CU(n-1)
  • The current (the one we want to apply) CU demo database – named here CU(n)
  • The current database based on CU(n-1) and your AddOns
  • A working folder Upgrade with 5 folders in it:
    • ORIGINAL
    • MODIFIED
    • TARGET
    • DELTA (used if you want to compare first the differences)
    • RESULT

Steps:

1. Create objects in text format for all objects of:

  • Demo database CU(n-1) -> in the ORIGINAL folder
  • Demo database CU(n) -> in the MODIFIED folder
  • Current database -> in the TARGET folder

Finsql command=ExportObjects, file=c:\Upgrade\Original\Orginal.txt, servername=myserver, database=Demo CU (n-1)

Finsql command=ExportObjects, file=c:\Upgrade\Modified\Orginal.txt, servername= myserver, database=Demo CU (n)

Finsql command=ExportObjects, file=c:\Upgrade\Target\Orginal.txt, servername= myserver, database=your_current_db_with_addons

2. In the NAV 2016 Development Shell run:

Merge-NAVApplicationObject -TargetPath .\TARGET -OriginalPath .\ORIGINAL -ModifiedPath .\MODIFIED -ResultPath .\RESULT -DateTimeProperty FromModified -ModifiedProperty FromModified -VersionListProperty FromModified

This will populate RESULT folder with txt files and conflict folders.

3. Merge code using BeyondCompare or any other similar tool comparing files from ConflictModified folder against correspondent files from ConflictTarget folder. Copy the merged files(overwrite existent) into the Result folder.

4. In your current database(target db) Development Environment select all standard objects that have been touched by your AddOns

5. Take each object of the list in Development Environment and manually add your AddOn version into the correspondent files from RESULT folder

Note: This is because the -VersionListProperty parameter of Merge_NAVApplicationObject cmdlet does not update the version list with the AddOn signature – need to do it manually for now.

6. Combine all text files from RESULT folder into one:

PS C:\upgrade\result> Join-NAVApplicationObjectFile -Source “*.txt” -Destination “all_objects.txt”

7. Import “all_objects.txt” in the development environment manually or using finsql’s command=importobject” parameter.

8. Compile all objects

More MSDN info here and here.

Useful Powershell commands:

  • Create Delta files

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL -ModifiedPath .\MODIFIED -DeltaPath .\DELTA

  • To apply Delta files:

Update-NAVApplicationObject –DeltaPath .\DELTA -TargetPath .\TARGET\*.txt -ResultPath .\RESULT

  • Join all text files into one:

Join-NAVApplicationObjectFile -Source “*.txt” -Destination “all_objects.txt”

  • Import that single file in NAV

finsql.exe command=importobjects, file=C:\Upgrade\Result\all_objects.txt, servername=myserver, database=”merge_target”

  • Split a big text file into individual text objects

Split-NAVApplicationObjectFile -Source C:\Upgrade\ORIGINAL\*.txt -Destination C:\Upgrade\ORIGINAL\TXT\

More MSDN info on cmdlets here.