Microsoft Gold Partner Logo_edited.png
  • LinkedIn Social Icon
  • Twitter Social Icon
  • Facebook Social Icon
  • YouTube Social  Icon

Austin  |  Dallas  |  Houston  |  New Orleans  |  Nashville

Dynamics NAV 2016 – How to use the Force.

According to Star Wars:

“The Force is what gives a Jedi his power. It’s an energy field created by all living things. It surrounds us and penetrates us. It binds the galaxy together.” – Obi Wan Kenobi

According to Microsoft the Force is a Sync-NAVTenant cmdlet Schema Synchronization Option

“Table definition changes are applied to the business data table schema without validation. For destructive changes, data in columns of the business data table that are affected by changes will be deleted.This option ignores any table synchronization instructions for the table in upgrade code units. You should use this option only when you are sure that there is no risk of unwanted data loss.”

With Dynamics NAV 2016 when you modify a table you must perform Database Schema Synchronization using the following options:

Now – with validation

Before applying changes to the business data table, Microsoft Dynamics NAV Server validates the changes to the table definitions to check whether they are destructive changes. This includes changes that will delete data in the fields of the business data table which are affected by the changes.

If there are no destructive changes to the table, then the schema changes are applied to the business data table immediately.

If there are destructive changes, Microsoft Dynamics NAV Server checks that there are table synchronization instructions in an upgrade code unit. If there are instructions, then the schema changes are applied to the business database table according to the instructions. If there are no instructions, then an error message appears. The table definition changes are not saved and the schema changes are not applied.

For more information about how Microsoft Dynamics NAV Server validates changes, see How Microsoft Dynamics NAV Server Validates Table Schema Changes.

Later

Table definition changes are saved and compiled in the application but the changes are not validated or applied to the business data table. You synchronize the table schema later by doing one of the following:

Save or compile the table from Object Designer in development environment and choose either the Now – with validation or Force synchronization option.

Use the Sync. Schema for All Tables option on the Tools menu.

Run the Sync-NAVTenant cmdlet from the Microsoft Dynamics NAV 2016 Administration Shell.

  1. To get a list of pending changes you can run the following SQL Command.

SELECT b.[Hash] AS [Hash],b.[Object Type],b.[Object ID],b.Name FROM [Demo Database NAV (9-0)].[dbo].[Object Metadata Snapshot] b LEFT JOIN [Demo Database NAV (9-0)].[dbo].[Object Metadata] a ON a.[Hash] = b.[Hash]WHERE a.[Hash] IS NULL;

Force

Table definition changes are applied to the business data table schema without validation. For destructive changes, data in columns of the business data table that are affected by changes will be deleted.

This option ignores any table synchronization instructions for the table in upgrade codeunits.

When using the Force you should consider the following:

  1. Depending on the size of the tables that you modify and the nature of changes, the schema synchronization process can take anywhere from several seconds to several hours (for example. this can occur when you add multiple fields or keys to a large table). Therefore, it is important that you control when schema synchronization is run.When synchronizing large tables, you should increase the SQL Command Timeout setting for the Microsoft Dynamics NAV Server instance that connects to the database to avoid timeouts during schema synchronization. The default setting is 30 minutes. For more information, see Configuring Microsoft Dynamics NAV Server.

  2. You should use this option only when you are sure that there is no risk of unwanted data loss.

  3. You should perform a SQL Backup of the database before running the Force command.

  4. Create a list of Sync Errors with Table and Field changes by running the following PowerShell Command.

$report = @()

$SyncResult = Sync-NAVTenant $ServiceTier -Mode ForceSync -ErrorAction SilentlyContinue -ErrorVariable SyncErrors If ($SyncErrors) {

foreach ($SyncError in $SyncErrors) { $SyncError.Exception.Message | Format-Table -AutoSize $lines = $SyncError.Exception.Message.Split(“`r`n”) foreach ($line in $lines) { $ObjectType = $line.Split(‘:’)[0] $ObjectAction = ” if ($ObjectType-eq”Table”) { $TableObjectType = $ObjectType $TableID = $line.Split(‘:’)[1] $TableID = $TableID.Split(‘,’)[0] $TableName = $line.Split(‘,’)[1]

$report += New-Object psobject -Property @{ParentObject=$ObjectType;ParentObjectID=$TableID;ObjectType=$ObjectType;ObjectID=$TableID;ObjectName=$TableName;ObjectAction=$ObjectAction}

}

if ($ObjectType-eq”Field”) { $ObjectID = $line.Split(‘:’)[1] $ObjectID = $ObjectID.Split(‘,’)[0] $ObjectName = $line.Split(‘,’)[1] $ObjectAction = $ObjectName.Split(‘:’)[1] $ObjectName = $ObjectName.Split(‘:’)[0] $report += New-Object psobject -Property @{ParentObject=$TableObjectType;ParentObjectID=$TableID;ObjectType=$ObjectType;ObjectID=$ObjectID;ObjectName=$ObjectName;ObjectAction=$ObjectAction} } } } }

$SyncErrors.Exception.Message | Out-File “Sync-Errors.txt” $report | Select-Object ParentObject,ParentObjectID,ObjectType,ObjectID,ObjectName,ObjectAction | export-csv “Sync-NAVTenantResults.csv” -NoTypeInformation Invoke-Item “Sync-NAVTenantResults.csv” Invoke-Item “Sync-Errors.txt” }

}

Now that you are a Jedi master at Schema Synchronization I hope you do not fall to the Dark Side of the Force Command.

May the force be with you.


#advancedbusinesssystems #DynamicsNAV2016