Get Table Data from a tenant database

I am working on a data upgrade for a customer. The existing database is from another partner and has tables and fields that my development license does not give me access to.

The database in in version 2016 and I am upgrading to Business Central.

So, I added some functions to the AdvaniaGIT PowerShell module.

To get the table definition from the tenant database I use

(Get-NAVTableMetaDataXml -DatabaseName <tenantDatabasename> -TableId 289 -Snapshot).OuterXml | Out-File C:\AdvaniaGIT\Workspace\xml.xml

The resulting Xml file I can view in any Xml viewer.

This data will help me update my upgrade tables for the Sync-NAVTenant command to complete successfully.

But, I wanted more. Since the destination for the data is in an App table I feel that i have no need for the data in the database during the C/AL upgrade.

I also created the Get-NAVTenanttableNos function that will give me a list of all tables in the tenant database. Using this data I was able to iterate through all the tables and export the structure and data.

I created an empty folder and executed

Get-NAVUpgradeTablesData -TenantDatabase <tenantDatabasename> -ApplicationDatabase 2018-App -CompanyName "MyCompany" -Verbose -ExportPath 'C:\AdvaniaGIT\Workspace\MyCompany'

This gives me all the data that does not fit in the standard 2018-App in separate Xml files .

Each file contains only the fields required to import the custom data. As an example, the xml file for table 17, G/L Entry, does not contain all the data from G/L Entries, just the primary key and the custom fields.

In the Xml file I include details for the table and for the fields included in the data set.

Custom tables that do not have any match in the 2018-App database are exported with all fields. With all the custom data stored safely I can execute the Sync-NAVTenant with Force to get the database upgraded.

The next task is in AL, writing the code to import the Xml files to the App tables.

I will be back with the results from that task…

Add translations to your NAV/BC Server

Yesterday I got a question via LinkedIn. I need to add Spanish translation to my W1 instance. How do I do that?

So, let me walk you through that process.

Here is my Business Central setup. It is the Icelandic Docker Container, so I have Icelandic and English. Switching between Icelandic and English works just fine.

Switching to Spanish gives me a mix of Spanish and English.

The Spanish translation for the platform is shipped with the DVD image and automatically installed. So are a lot of other languages.

Icelandic and English are built in captions in the C/AL code. And even if all these languages are shipped with the platform, these languages are not shipped with the application.

There is a way to get these application translations from the appropriate release and add them to your application.

Let’s start in VS Code where I have cloned my Business Central repository from GitHub. I opened the workspace file and also opened “setup.json” from the root folder of my repository.

This configuration points to the W1 Business Central OnPrem Docker Image. Now, let’s point to the Spanish one.

And let’s build a container.

Switching the Terminal part to AdvaniaGIT, I see that I am now pulling the Spanish Docker image down to my laptop.

This may take a few minutes…

After the container is ready I start FinSql.exe

Just opening the first table and properties for the first field I can verify than I have the Spanish captions installed.

So, let’s export these Spanish captions by selecting all objects except the new trigger codeunits (Business Central only) and selecting to export translation…

Save the export to a TXT file.

Opening this file in Visual Studio Code, we can see that the code page does not match the required UTF-8 format. Here we can also see that we have English in lines with A1033 and Spanish in lines with A1034.

We need to process this file with PowerShell. Executing that script can also take some time…

This script reads the file using the “Oem” code page. This code page is the one FinSql uses for import and export. We read through the file and every line that is identified as Spanish is the added to the output variable. We end by writing that output variable to the same file using the “utf8” code page.

Visual Studio Code should refresh the file automatically.

We need to create a “Translations” folder in the server folder. The default server uses the root Translations folder.

If you have instances then the “Translations” folder needs to be in the Instance.

Since I am running this in a container I may need to create this folder in the container.

Then, copy the updated file to the “Translations” folder.

And make sure it has been put into the correct path.

We need to restart the service instance.

Then in my Web Client I can verify that the Spanish application language is now available.

That is it!

Here is the PowerShell script

$LanguageFile = Get-Item -Path C:\AdvaniaGIT\Workspace\es.txt

Write-Host "Loading $($LanguageFile.Name)..."
$TranslateFile = Get-Content -Path $LanguageFile.FullName -Encoding Oem
$i = 0
$count = $TranslateFile.Length
$StartTime = Get-Date
foreach ($Line in $TranslateFile) {
    $NowTime = Get-Date
    $TimeSpan = New-TimeSpan $StartTime $NowTime
    $percent = $i / $count
    if ($percent -gt 1) 
        $percent = 1
    $remtime = $TimeSpan.TotalSeconds / $percent * (1-$percent)
    if (($i % 100) -eq 0) 
        Write-Progress -Status "Processing $i of $count" -Activity 'Updating Translation...' -PercentComplete ($percent*100) -SecondsRemaining $remtime

    if ($Line -match "A1034") {
        if ($TranslatedFile) {
            $TranslatedFile += $Line + "`r`n"
        } else {
            $TranslatedFile = $Line + "`r`n"

Write-Host "Saving $($LanguageFile.Name)..."
Remove-Item -Path $LanguageFile.FullName -Force -ErrorAction SilentlyContinue
Out-File -FilePath $LanguageFile.FullName -Encoding utf8 -InputObject $TranslatedFile -Force

In this post I used both AdvaniaGIT and NAVContainerHelper tools. Good luck.

Using AdvaniaGIT in Visual Studio Code

It has become obvious that the future of AL programming is in Visual Studio Code.

Microsoft has made a decision to ship all their releases as Docker Containers.

The result of this is a development machine that does not have any NAV version installed.  I wanted to go through the installation and configuration of a new NAV on Docker development machine.

Here is what I did.

I installed Windows Server 2016 with Containers.  The other option was to use Windows 10 and install Docker as explained here.

After installing and fully updating the operating system I downloaded and installed Visual Studo Code.

After installation Visual Studio Code detects that I need to install Git.

I selected Download Git and was taken to the Git download page.

I downloaded and installed Git with default settings.

To be able to run NAV Development and NAV Client I need to install prerequisite components.  I copied the Prerequisite Components folder from my NAV 2018 DVD and installed some of them…

Let’s hook Visual Studio Code to our NAV 2018 repository and install AdvaniaGIT.  I first make sure to always run Visual Studio Code with administrative privileges.

Now that we have our AdvaniaGIT installed and configured we can start our development.  Let’s start our C/AL classic development.  Where this video ends you can continue development as described in my previous posts on AdvaniaGIT.  AdvaniaGIT also supports NAV 2016 and NAV 2017.

Since we are running NAV 2018 we can and should be using AL language and the Extension 2.0 model.  Let’s see how to use our repository structure, our already build Docker container and Visual Studio Code to start our first AL project.

So as you can see by watching these short videos it is easy to start developing both in C/AL and AL using AdvaniaGIT and Visual Studio Code.

My next task is to update my G/L Source Names extension to V2.  I will be using these tools for the job.  More to come soon…

Introducing AdvaniaGIT – SCM for Dynamics NAV

Almost two years ago we in Advania decided to start using GIT as Source Control Management (SCM).  We brought Kamil up to Iceland and we kicked off.  In Sorens session on NAVTechDays last year we demoed SourceTree as the GIT client for NAV SCM.

Everything we in Advania are doing with SCM is available on GitHub.  It is our hope that we can get as many users and companies to use and contribute to this solution.

Over the next coming days and weeks I will be writing here about this tool.  I will also be using the GitHub Wiki for some of the information.

Installing AdvaniaGIT will create a folder structure on your local drive. You can select any of the local drive installed. We suggest that the AdvaniaGIT\Workspace folder should be excluded from Windows Defender and that also goes for any GIT folder used.

Refer to the file inside every subfolder for more details about each subfolder usage.

Inside the Data subfolder we store the module settings in JSON files.

  • BranchSettings.json is automatically managed by the module and used to link GIT branches to local NAV environments.
  • BuildSettings.json contains incremented values that will be used when building new environments.
  • GITSettings.json contains machine settings for the module.
  • NAVVersions.json contains information about locally installed NAV.
  • RemoteSettings.json contains settings for the Remote Management module. Not used by GIT in any way.
  • TenantSettings.json contains settings for each tenant running on a remote server that is managed using the Remote Management module. Not used by GIT in any way.

In the GIT repository folder we require a setup.json file. When the scripts are executed settings from the GIT branch (setup.json) and settings from the machine (GITSettings.json) are merged to a single settings object. If same settings exist in both files the one in the GIT branch will be used.

Installing the module will add custom actions to SourceTree and a command file (StartPowerShell.cmd) to your Windows directory. SourceTree will execute this command file with parameters telling the module what to do. The command file will execute Scripts\Start-CustomAction.ps1 with the same parameters. All custom actions within the Scripts\CustomActions subfolder can be executed.

For teams we suggest using a FTP server for backups and CRONUS text files.

My next blog post will be on the installation and update of AdvaniaGIT.  Stay tuned…

Using NetTcpPortSharing for NAV Servers

I just came back from three weeks vacation yesterday.  During my vacation I had made a decision to implement Tcp Port Sharing for the Instance Administration tool used in Advania Azure.

Early last year I published a function that uses the sc.exe to modify a NAV Service startup type.  When a NAV Service is installed and configured in setup, the startup type is Automatic (Delayed Start).  However, create a new service with Powershell New-NavServerIntance and the statup type is Automatic without the (Delayed Start).

To enable Tcp Port Sharing that same sc.exe function is needed.  Interestingly, after I had finished the task and was reading NAV blogs I saw that Waldo just published a powershell function on his blog to do just this.

The script lines I used and added to my Instance Administration powershell scripts are based on my fist sc.exe function but not using the function it self.  Now when a new NAV service is created by the tool the startup type is modified and if so selected by the deployment settings, the Tcp Port Sharing is also activated.

By default, the Tcp Port Sharing service is disabled.

The startup type should be changed to Manual.  This can be done manually or by an administrative powershell script.

[code lang=”powershell”]#Set Startup Mode for NetTcpPortSharing to Manual
$command = ‘sc.exe \\$Computer config "NetTcpPortSharing" start= demand’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set NetTcpPortSharing to manual start. More details: $Output"

Similar script is used to update the existing NAV Services to both delayed start and Tcp Port Sharing dependency.

[code lang=”powershell”]

#Stop NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Stop
#Update Startup Type and Dependency on NAV Server Instances
Get-NAVServerInstance | foreach {
$Service = $_.ServerInstance
Write-Host "Working on service $Service"
$Computer = ‘LOCALHOST’
$command = ‘sc.exe \\$Computer config "$Service" start= delayed-auto’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service to delayed start. More details: $Output"
$command = ‘sc.exe \\$Computer config "$Service" depend= NetTcpPortSharing/HTTP’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service TcpPortSharing. More details: $Output" -foregroundcolor red

#Start NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Start

It should be obvious that the above script can also use the Set-ServiceStartupMode from my blog and the Enable-NAVServerInstancePortSharing function on Waldo’s blog. That would be a cleaner code and more in line with what we would like to see.

Again quoting Waldo from his previous blog, “When you’re using a dedicated service account, things might become a slight more difficult”.  That is exactly my case, I am using a dedicated service account.

After enabling Tcp Port Sharing and updating the services they would not start.  Event Viewer revealed the reason.

Server instance: CRONUS
The service MicrosoftDynamicsNavServer$CRONUS failed to start. This could be caused by a configuration error. Detailed error information:System.ServiceModel.CommunicationException: The service endpoint failed to listen on the URI ‘net.tcp://’ because access was denied. Verify that the current user is granted access in the appropriate allowAccounts section of SMSvcHost.exe.config. —> System.ComponentModel.Win32Exception: Access is denied

So I started to ask Bing what I could do.  Microsoft MSDN states:

When a net.tcp binding enables port sharing (by setting portSharingEnabled =true on the transport binding element), it implicitly allows an external process (namely the SMSvcHost.exe, which hosts the Net.TCP Port Sharing Service) to manage the TCP socket on its behalf.

Hence, I need to add the Sid of my NAV Service Account to the SMSvcHost.exe.config file.  I could do this manually, but I am a programmer!

Another powershell script was born.  This one could also be converted to a function.  Before executing the script make sure to update the user and domain in the top of the script.  Be smart and execute this function before updating the NAV Services with the script above.

[code lang=”powershell”]
#Modify User and Domain to fit your environment
$UserToAdd = ‘srvNAV’
$UserDomainToAdd = ‘DYNAMICS’

#Initial Values
$UserSidFound = ‘false’
$ConfigurationSet = ‘false’

#Net.Tcp Port Sharing Service Name
$ServiceName = ‘NetTcpPortSharing’

#Get SID for the Service User
$UserSid = ([wmi] "win32_userAccount.Domain=’$UserDomainToAdd’,Name=’$UserToAdd’").SID

#Get Path for SMSvcHost.exe.config file
$SMSvcHostPath = (Get-WmiObject win32_service | ?{$_.Name -like $ServiceName} ).PathName
$SMSvcHostPathConfig = $SMSvcHostPath + ‘.config’

Write-Host "Reading XML from $SMSvcHostPathConfig"
#Read Config file
$xmlDoc = [xml] (Get-Content $SMSvcHostPathConfig)

Write-Host "Looking for access permission for $UserSid"
#Loop through allowed accounts and search for the service user Sid
$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’
Write-Host "Found SID $ConfiguredSid"

#Act if Access Configuration is not enabled
if ($ConfigurationSet -eq ‘false’) {Write-Host "Access permission not configured"
$config = [xml] ‘<system.serviceModel.activation>
<net.tcp listenBacklog="10" maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10" teredoEnabled="false">
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
<net.pipe maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10">
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
<diagnostics performanceCountersEnabled="true" />

$configurationNode = $xmlDoc.DocumentElement
$newConfig = $xmlDoc.ImportNode($config.DocumentElement, $true)

$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
Write-Host "Found SID $ConfiguredSid"
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’


#Add Service User Sid if needed
if ($UserSidFound -ne ‘true’) {
$nettcp = $xmlDoc.SelectSingleNode("configuration/system.serviceModel.activation/net.tcp/allowAccounts")
$addNode = $xmlDoc.CreateElement(‘add’)
$secIden = $xmlDoc.CreateAttribute(‘securityIdentifier’)
$secIden.Value = $UserSid

Write-Host "Configuration Updated"
#Restart Service if running
if ((Get-Service NetTcpPortSharing).Status -eq "Running") {Restart-Service NetTcpPortSharing -Force}


This script will search for the SMSvcHost.exe.config file, load it and check to see if the NAV Service User is already allowed access.  If not then the config file is updated and saved.  This script must be executed with administrative privileges.

Perhaps this should be what I started with, but the question; why do we need this, should be answered.

First, modifying the startup mode to delayed start is done to make sure that all the required networking and database processes have been started before the NAV Service starts.  This is very important if the SQL Server is running on the same server.  On a dedicated NAV Service server this is not as important but still recommended.

Secondly, accessing a NAV Service in most cases requires changes to a firewall.  Either to open a specific port or setting up a NAT from a public interface.  To minimize the number of ports used also minimizes the networking setup and maintenance.  If different network permissions or network access is required I recommend using separate ports for the NAV Services.

Building a clean database – remove not licensed objects

I just got a question from a client;

Do you have a “King Kong” license that will allow you to delete any object?  It appears our development license does not have the rights to some of the newer LS Retail objects and I need to create a CRONUS database with just our stuff.

Well, I don’t have a “King Kong” license.  That is only for Microsoft.

There is a way to solve this dilemma.  It will take a few steps.

Start with we have two databases, one with the data we need (LSRetail), another with the application we need (CRONUS).

After the process is completed the LSRetail database will not be usable as a standalone database, so make a copy if you need one.  A new database will be created, CRONUS_APP.  To clean up it is safe to delete both these databases.

The following powershell script has two options.  Option 1 is to have the company data imported into the CRONUS database in the end.  This option requires a server instance running on the CRONUS database.  Option 2 is to create a new database with SQL Management Studio and merge the CRONUS application and the LSRetail data into that one.

[code lang=”powershell”]
$CronusDatabaseName = "CRONUS" # Database with destination Application
$CRONUSServerInstance = "DynamicsNAV80" # Instance for destination Application if using option 1
$LSRetailDatabaseName = "LSRETAIL" # LS Retail Demo Database, database with company data
$EmptyDatabaseName = "CRONUS WITH COMPANYDATA" # Create a new empty database using SQL Management Studio if using option 2
$SQLServerName = "SQL2014"
$SQLServerInstance = "NAVDEMO" # Set blank for default instance

$AppDatabaseName = $CronusDatabaseName + "_APP"
$ServiceAccount = $env:USERDOMAIN + "\" + $env:USERNAME
$ServerInstance = "UPGRADE"
$NavDataFile = (Join-Path $env:TEMP "NAVmerge.navdata")

$SelectOption = "2"

#Export Application from CRONUS Database to Application Database
Export-NAVApplication -DatabaseServer $SQLServerName -DatabaseInstance $SQLServerInstance -DatabaseName $CronusDatabaseName -DestinationDatabaseName $AppDatabaseName -ServiceAccount $ServiceAccount -Force

#Setup a temporary Server Instance for the new database
Get-Credential | New-NAVServerInstance -ServerInstance $ServerInstance -ManagementServicesPort 33555 -ClientServicesPort 33556 -SOAPServicesPort 33557 -ODataServicesPort 33558 -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $AppDatabaseName -ServiceAccount User -Force
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName "Multitenant" -KeyValue "true" -Force
Set-NAVServerInstance -ServerInstance $ServerInstance -Start -Force

#Prepare LSRetailDatabase for new configuration
Remove-NAVApplication -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Force

#Mount and Sync LSRetailDatabase as a tenant
Mount-NAVTenant -ServerInstance $ServerInstance -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Id DEFAULT -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite -Force
Sync-NAVTenant -ServerInstance $ServerInstance -Tenant DEFAULT -Mode ForceSync -Force

if (Test-Path $NavDataFile)
Remove-Item -Path $NavDataFile -Force

#Option 1, Copy Company data to the original CRONUS database. Requies a service running on the CRONUS database
if ($SelectOption -eq "1")
Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -Force
Import-NAVData -ServerInstance $CRONUSServerInstance -FilePath $NavDataFile -AllCompanies -Force
#Option 2, Import into the new empty database created by SQL Management Studio
if ($SelectOption -eq "2")

Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -IncludeApplication -IncludeApplicationData -IncludeGlobalData -Force
if ($SQLServerInstance -eq "")
Import-NAVData -DatabaseServer $SQLServerName -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication

Import-NAVData -DatabaseServer ($SQLServerName + "\" + $SQLServerInstance) -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication


Set-NAVServerInstance -ServerInstance $ServerInstance -Stop -Force
Remove-NAVServerInstance -ServerInstance $ServerInstance -Force

if (Test-Path $NavDataFile)
Remove-Item -Path $NavDataFile -Force

To walk you through what happens;

  • Application from CRONUS is exported into CRONUS_APP database
  • New Service Instance is created for CRONUS_APP database
  • Service Instance is changed to Multi Tenant and started
  • Application is removed from LSRetail database
  • LSRetail database is mounted as a tenant for CRONUS_APP database
  • LSRetail database structure is force-synched to CRONUS_APP application
  • Data from CRONUS_APP and LSRetail tenant is exported to NAVData file
  • NAVData file is imported into an empty database or the existing CRONUS database


Apply a Cumulative Update with Powershell

Microsoft has changed the way they ship Cumulative Updates.  Now we download the whole DVD image along with the application changes.

This post is intended as additional information for New Developer Tools for Dynamics NAV 2013 R2 and Install Client and Server update with PowerShell posts.

To update a code that is built on a RTM version or an older CU version I use the Merge-NAVToNewCU script package.  I create a temporary folder and export three set of objects.  One file for the original unchanged objects (38457Objects.txt), One file for the new CU objects (40938Objects.txt) and finally the modified version (38457customizedObjects.txt).  When I export I normally skip the MenuSuites that I don’t have permission to import.

Microsoft sometimes adds new fields to tables, fields that are outside of my permission to insert.  Therefore, when I have completed the compare and have my new customized object (40938customizedObjects.txt) I start by importing the object fob file directly from the Microsoft Dynamics NAV Application folder found in the CU package.  I accept the default action, replace most objects and merge tables.  This will create all the new fields needed by the update.  After I am able to import the new customized objects text file and compile.

[code lang=”powershell”]$rootFolderName = $PSScriptRoot
$oldVersion = ‘38457’
$newVersion = ‘40938’

Import-Module "${env:ProgramFiles(x86)}\Microsoft Dynamics NAV\80\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1" -force
Import-Module (Join-Path $rootFolderName ‘Merge-NAVVersionListString script.ps1’) -force

$diffFolderName = (Join-Path $rootFolderName ($oldVersion + ‘to’ + $newVersion + ‘diff’))
$oldObjects = (Join-Path $rootFolderName ($oldVersion + ‘objects.txt’))
$newObjects = (Join-Path $rootFolderName ($newVersion + ‘objects.txt’))
$newFolder = (Join-Path $rootFolderName ($newVersion + ‘update’))
$customObjects = (Join-Path $rootFolderName ($oldVersion + ‘customizedobjects.txt’))
$newCustomizedObjects = (Join-Path $rootFolderName ($newVersion + ‘customizedobjects.txt’))
$newCustomizedFolder = (Join-Path $rootFolderName ($newVersion + ‘customized’))
if (!(Test-Path $diffFolderName))
mkdir $diffFolderName
if (!(Test-Path $newFolder))
mkdir $newFolder
if (!(Test-Path $newCustomizedFolder))
mkdir $newCustomizedFolder
Write-Host "Comparing customized and original…"
Compare-NAVApplicationObject -Original $oldObjects -Modified $customObjects -Delta $diffFolderName | Where-Object CompareResult -eq ‘Identical’ | foreach { Remove-Item (Join-Path $diffFolderName ($_.ObjectType.substring(0,3) + $_.Id + ‘.delta’)) }
Write-Host "Splitting new objects…"
Split-NAVApplicationObjectFile $newObjects $newFolder
Write-Host "Removing unchanged new objects…"
Get-ChildItem -Path $newFolder | foreach { if (!(Test-Path ((Join-Path $diffFolderName $_.BaseName) + ‘.delta’))) { Remove-Item $_.FullName } }
Write-Host "Updating new objects…"
Update-NAVApplicationObject -Target $newFolder -Delta $diffFolderName -Result $newCustomizedFolder -DateTimeProperty FromModified -ModifiedProperty FromModified -VersionListProperty FromModified -DocumentationConflict ModifiedFirst
Write-Host "Updating customized object version list…"
Get-ChildItem -Path (Join-Path $newCustomizedFolder ‘*.txt’)| foreach { if (Test-Path (Join-Path $newFolder $_.Name)) {Set-NAVApplicationObjectProperty -Target $_.FullName -VersionListProperty (Merge-NAVVersionListString -source (Get-NAVApplicationObjectProperty -Source $_.FullName).VersionList -target (Get-NAVApplicationObjectProperty -Source (Join-Path $newFolder $_.Name)).VersionList) }}
Write-Host "Joining customized object to a single file…"
Join-NAVApplicationObjectFile -Source (Join-Path $newCustomizedFolder ‘*.txt’) -Destination $newCustomizedObjects
Write-Host "If you have conflicts then you need to manually fix conflicting code changes"

I have also created a set of scripts to update the Binaries.  For some time now Microsoft has not shipped the binary upgrade folders with the CU package.  Now they deliver the whole DVD, including the new updated Demo Database.  The scripts therefore must copy the files from the DVD instead of copying from the binary upgrade folders.

When copying from the DVD we must make sure that we don’t overwrite the configuration files for the server and the web client. This is the server update script. All server on the computer will be stopped, updated and restarted.

[code lang=”powershell”]$NAVDVDFilePath = ‘\\STORAGE\NAV 2015\NAV.8.0.40938.IS.DVD’
$NotToCopy = @(‘Tenants.config’,’CustomSettings.config’)
Write-Verbose "Copying NAV Server Update…"
$ClientKBFolder = Join-Path $NAVDVDFilePath ‘ServiceTier\program files\Microsoft Dynamics NAV\80\Service’
$navInstallationDirectory = Join-Path ${env:ProgramFiles} ‘Microsoft Dynamics NAV\80\Service’
if (Test-Path $navInstallationDirectory)
Import-Module (Join-Path $ClientKBFolder ‘Microsoft.Dynamics.Nav.Management.dll’) -DisableNameChecking | Out-Null
$RunningInstances = Get-NAVServerInstance | Where-Object { $_.State -eq "Running" }
Write-Verbose "Stopping Server Instances…"
Get-NAVServerInstance | Where-Object { $_.State -eq "Running" } | Set-NAVServerInstance -Stop
Start-Sleep -s 5

Write-Verbose "Running file copy command…"
Get-ChildItem -Path $ClientKBFolder | % {Copy-Item $_.FullName $navInstallationDirectory -Recurse -Force -Exclude $NotToCopy}

Write-Verbose "Done updating files…"
foreach ($RunningInstance in $RunningInstances)
$InstanceName = $RunningInstance.ServerInstance.ToString()
Write-Verbose "Starting Server Instance $InstanceName"
Get-NAVServerInstance -ServerInstance $InstanceName | Set-NAVServerInstance -Start

This is the web server update script

[code lang=”powershell”]$NAVDVDFilePath = ‘\\STORAGE\NAV 2015\NAV.8.0.40938.IS.DVD’
$NotToCopy = (‘web.config’,’instanceweb.config’,’Header.png’,’About.png’,’Splash.png’)
function Copy-LatestFile
param (
Write-Verbose "Running file copy command…"
$sourcefiles = Get-ChildItem $SourceDirectoryPath -Recurse
$destfiles = Get-ChildItem $DestinationDirectoryPath -Recurse
foreach ($sourcefile in $sourcefiles)
$copyfile = $false
foreach($destfile in $destfiles)
if ($destfile.Name -eq $sourcefile.Name)
$copyfile = $true
if (!($copyfile) -or ($NotToCopy -match $sourcefile.BaseName))
write-verbose "not copying $sourcefile…"
write-verbose "copying $sourcefile…"
Copy-Item $sourcefile.FullName $destfile.FullName -Force
Write-Verbose "Copying Web Client Update…"
$ClientKBFolder = Join-Path $NAVDVDFilePath ‘WebClient\Microsoft Dynamics NAV\80\Web Client’
$navInstallationDirectory = Join-Path ${env:ProgramFiles} ‘Microsoft Dynamics NAV\80\Web Client’
if (Test-Path $navInstallationDirectory)
Copy-LatestFile -SourceDirectoryPath $ClientKBFolder -DestinationDirectoryPath $navInstallationDirectory

And finally the client update script.

[code lang=”powershell”]$NAVDVDFilePath = ‘\\STORAGE\NAV 2015\NAV.8.0.40938.IS.DVD’
$NotToCopy = (‘Header.png’,’About.png’,’Splash.png’)
function Copy-LatestFile
param (

Write-Verbose "Running file copy command…"
$sourcefiles = Get-ChildItem $SourceDirectoryPath -Recurse -File
$destfiles = Get-ChildItem $DestinationDirectoryPath -Recurse -File

foreach ($sourcefile in $sourcefiles)
$copyfile = $false
foreach($destfile in $destfiles)
if ($destfile.Name -eq $sourcefile.Name)
$copyfile = $true
if (!($copyfile) -or ($NotToCopy -match $sourcefile.BaseName))
write-verbose "not copying $sourcefile…"
write-verbose "copying $sourcefile…"
Copy-Item $sourcefile.FullName $destfile.FullName -Force

Write-Verbose "Copying RTC Update…"
$ClientKBFolder = Join-Path $NAVDVDFilePath ‘RoleTailoredClient\program files\Microsoft Dynamics NAV\80\RoleTailored Client’
$navInstallationDirectory = Join-Path ${env:ProgramFiles(x86)} ‘Microsoft Dynamics NAV\80\RoleTailored Client’
if (Test-Path $navInstallationDirectory)
Copy-LatestFile -SourceDirectoryPath $ClientKBFolder -DestinationDirectoryPath $navInstallationDirectory
Write-Verbose "Copying Office 14 Update…"
$ClientKBFolder = Join-Path $NAVDVDFilePath ‘Outlook\program files\Microsoft Dynamics NAV\80\OutlookAddin’
$navInstallationDirectory = Join-Path ${env:ProgramFiles(x86)} ‘Microsoft Office\Office14’
if (Test-Path $navInstallationDirectory)
Copy-LatestFile -SourceDirectoryPath $ClientKBFolder -DestinationDirectoryPath $navInstallationDirectory
Write-Verbose "Copying Office 15 Update…"
$ClientKBFolder = Join-Path $NAVDVDFilePath ‘Outlook\program files\Microsoft Dynamics NAV\80\OutlookAddin’
$navInstallationDirectory = Join-Path ${env:ProgramFiles(x86)} ‘Microsoft Office\Office15’
if (Test-Path $navInstallationDirectory)
Copy-LatestFile -SourceDirectoryPath $ClientKBFolder -DestinationDirectoryPath $navInstallationDirectory

The ClickOnce distribution is always based on the client folder and the script in Install Client and Server update with PowerShell can be used for that purpose.

I am using these binary update scripts in the Instance and Tenant Administration tool and I normally maintain the ClickOnce distribution from there.



New Developer Tools for Dynamics NAV 2013 R2

Cumulative update 9 for Dynamics NAV 2013 R2 has been released.  This new version includes a powershell tool package for developers.

You can read about these tools in the NAV Team Blog, on Soren’s Blog and on Waldo’s Blog.

With every CU package we get an upgrade for the application.  I have a customer that is already running CU8 and I want to install CU9.  This time I will do that with these new powershell tools.

First step is to install the new CU9 binaries.  This I will do with the powershell scripts from here.  This will update windows client files, web client files, server files, outlook integration and click once deployment.  On my developement machine I needed to copy the RTC client files from the CU9 DVD.  The new model tools where not included in the KB RTC folder.

Then to export the Dynamic NAV objects.

I use my CU8 version demo database and export all objects to cu8objects.txt.  I export all objects from the customized database to cu8customizedobjects.txt.  I finally use my CU9 version demo database and export all objects to cu9objects.txt.

Open PoweShell ISE and navigate to the folder with the object files.

Directory: N:\nav\cu9
Mode                LastWriteTime     Length Name
 ----                -------------     ------ ----
 -a---         14.7.2014     09:32  154023304 cu8customizedobjects.txt
 -a---         14.7.2014     09:27  120451925 cu8objects.txt
 -a---         14.7.2014     09:35  120463442 cu9objects.txt

Next I import the new model tools.

Import-Module "${env:ProgramFiles(x86)}\Microsoft Dynamics NAV\71\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1" -force

I create a folder called cu8tocu9diff and execute

Compare-NAVApplicationObject -Original .\cu8objects.txt -Modified .\cu9objects.txt -Delta .\cu8tocu9diff
Processed 4036 objects:
 Inserted   0 objects
 Deleted    0 objects
 Changed    64 objects
 Identical  3972 objects
 Failed     0 objects

I see in my cu8tocu9diff folder that I have 4.036 objects and 3.972 of them are not needed.  I deleted all the files from cu8tocu9diff folder and executed

Compare-NAVApplicationObject -Original .\cu8objects.txt -Modified .\cu9objects.txt -Delta .\cu8tocu9diff | Where-Object CompareResult -eq 'Identical' | foreach {  Remove-Item (Join-Path .\cu8tocu9diff ($_.ObjectType.substring(0,3) + $_.Id + '.delta')) }

The result is that I only have the 64 delta files needed in the cu8tocu9diff folder.

Now I need the same 64 objects from the customized database.  I begin by splitting the exported object file into a new folder.

Split-NAVApplicationObjectFile .\cu8customizedobjects.txt cu8customized

I now have all 4.036 objects in the cu8customized folder.  I delete all unneeded files by executing

Get-ChildItem -Path .\cu8customized | foreach { if (!(Test-Path ((Join-Path .\cu8tocu9diff $_.BaseName) + '.delta'))) { Remove-Item $_.FullName } }

I am now in a place to update the customized objects with the cu9 changes.

Update-NAVApplicationObject -Target .\cu8customized -Delta .\cu8tocu9diff -Result cu9customized.txt -DateTimeProperty FromModified -ModifiedProperty FromModified -VersionListProperty FromModified -DocumentationConflict ModifiedFirst
 Completed the update. Processed 64 application object(s) with a total of 190 individual change(s).
 100,0% of the individual changes were automatically applied during the update.
 Processed 64 application object(s):
Updated      64 objects – with changes in DELTA that were successfully merged with any changes from TARGET
 into RESULT.
 Conflict      0 objects – with changes in both DELTA and TARGET that could only be partially updated.
 The partially updated objects and the corresponding .CONFLICT files are added to RESULT.
 This also includes objects that are deleted in DELTA and changed in TARGET.
 Inserted      0 objects – that exist in DELTA that do not exist in TARGET and are inserted into RESULT.
 Deleted       0 objects – that are deleted in DELTA and exist in TARGET.
 Unchanged     0 objects – that exist in TARGET, are not changed in DELTA, and are copied from TARGET to RESULT.
 Failed        0 objects – that could not be imported, such as an object that is not valid or that contains
 unsupported features.
Processed 190 changes:
Conflict      0 changes
 Updated   100,0% of all changes

When I updated the customized objects with these 64 changes I got objects with a new version list.  The version list needs to be merged and for that I use a script that was written by NAV MVP Kamil Sacek.  The script is attached here as Merge-NAVVersionListString script and his scripts are available on CodePlex.  After copying the script to my base folder I need to import the new functions.

Import-Module '.\Merge-NAVVersionListString script.ps1'

And to merge the version list for the new objects I use

Get-ChildItem -Path .\cu9customized | foreach { Set-NAVApplicationObjectProperty -Target $_.FullName -VersionListProperty (Merge-NAVVersionListString -source (Get-NAVApplicationObjectProperty -Source $_.FullName).VersionList -target (Get-NAVApplicationObjectProperty -Source (Join-Path .\cu8customized $_.Name)).VersionList) }

I have now 64 updated object that I can import into my customized database and compile.  First I want to join all these 64 files into one object file.

Join-NAVApplicationObjectFile -Source .\cu9customized -Destination cu9customizeobjects.txt

When I imported the new customized object I got an error.  Error because I had new fields in tables that I did not have permission to create.  To solve this I begin by importing the URObjects.IS.37221.fob file that is included in the cumulative update package.  I accept the default action so all new fields should be merged into the existing tables.

Scripts are available to export and import NAV objects so this whole process can be scripted and automated.

Unblock downloaded files

In the process of downloading a file from an insecure location Windows blocks the file.

I have had this bothering to often.  For example when I downloaded an add-in and hit an error.

And more recently I downloaded a knowledge base packed and installed with my PowerShell script just to find out that every file in the download had been blocked.

Once again, PowerShell came to the rescue.  Opened Windows PowerShell ISE in Administration Mode and execute the below script for the needed folders.

[code lang=”ps”]$folder = ‘C:\PUB’
#$folder = ‘C:\Program Files\Microsoft Dynamics NAV’
#$folder = ‘C:\Program Files (x86)\Microsoft Dynamics NAV’
$files = Get-ChildItem -Path $folder -Recurse
foreach ($file in $files)
Write-Host "Unblocking file $file…"
Unblock-File -Path $file.FullName

Install Client and Server update with PowerShell

It is time to share with you the scripts that I am using to install and update the NAV application in the company network.  In the attached ZIP packages you will find a lot of files.

In the root folder I keep the settings files.  These files must be customized.  The folders are as follows;

  • Edge3-Internet – my internet facing server.  Here I install ClickOnce, WebClient and NAV Service.
  • Fjarvinna-Domain – my remote desktop server.  Here I install KB updates remotely.
  • Localhost-Domain – my intranet server.  Here I install NAV Service for the domain.
  • NAVCmdLets – my collection of PowerShell scripts for NAV.  Some a copied from the NAV DVD.
  • Install-KBLocally – scripts to install a KB package to the local computer.

If we first look at the Edge3-Internet folder, here I have scripts to remotely install NAV on a server.  I usually create a ClickOnce container in the folder ‘c:\inebpub\ClickOnce’ and keep all my ClickOnce subfolders in there.  In the Set-MachineSettings.ps1 file I set the machine name.

Next, in Fjarvinna-Domain folder I only have scripts to upload and install a KB package.  Also in here I have the Set-MachineSettings.ps1 to define the machine name.

The Localhost-Domain folder includes scripts to install NAV Service and NAV Users to the localhost.

Then there are the functions that I use in the above scripts.  In the NAVCmdLets folder I have a collection of scripts to handle ClickOnce, KB install and some SQL administration tasks.

Finally the Install-KBLocally folder.  As the name suggest, the contained scripts are used to install a KB package on the localhost.  I usually create a folder that contains this folder, the NAVCmdLets folder and for example the KB2955941 folder.  The KB2955941 can be downloaded from Microsoft.  I usually delete all sub folders except ADCS, NST, OUTLOOK, RTC and WEB CLIENT after I add this folder to the update package.

Also, in my KB package I have two CMD files.  One to install to a 64bit machine (default) and the other to a 32bit machine.  If you put this package on your network and modify the CMD files to point to the correct file share you should be able to use this for all the computers on the domain.  The KB package without the Microsoft binaries is attached below.  Make sure to right-click on the install CMD file and run as administrator for a succesful install.

If you are running an old operating system, you might need KB2506146 or KB2506143 installed before using the KB package.

With your network administration tools you should be able to use some of these scripts to install a KB package on all your domain computers to make sure that everyone is running the latest version recommended by Microsoft.

NAVPowerShellScripts KB