PIPrint – A Free Direct Printing Solution for your Microsoft Dynamics 365 Business Central

…and Now for Something Completely Different…

Some of you might recall that this blog is not limited to posts about Echelon/NES meters, and know it is actually for all kind of stuff Graves and I am dabbling with. Granted, lately it’s been mostly about our Echelon/NES project – but we have not stopped dabbling with other stuff too :-).

You might know that my day job is to develop solutions in Microsoft Dynamics 365 Business Central (I’ll call it BC in this entry), and for some time I had an idea I would like to share with the community:

As some of you might know, the BC web client and Microsoft’s chosen path has made it quite an expensive challenge to do direct print (direct print is when you do a print directly to a printer – without the user having to i.e., download a PDF and then print it manually).

Microsoft do have a solution called Universal Print and BC supports it along with e-mail enabled printers. You can read more about both solutions here! The main problem – if you printer does not support e-mail printing, is that Universal Print is quite expensive.

Others also have solutions for this – and it might even be included in something more advanced like ForNAV Direct Print.

But why didn’t someone design and do a cheap, Do-It-Yourself solution?

…and – if you are on BC SaaS, but still need a simple, cheap solution to print on local and cheap printers? Or even Network printers that does not support e-mail printing?

And off cause we should try to keep it as cheap and simple as possible – and don’t rely on any special ports to be forwarded and open in firewall etc. That is simply too cumbersome for regular users to setup!


BC App – maybe a Per Tenant Extension?

Well, imagine installing an app on your BC that collects all printjobs as PDF and store them in a BC table. Then making them available through a webservice of some kind… While at it we could do a secondary webservice allowing “Print Processors” (I’ll explain what that is in a minute) to register printers with BC, so they are available for printing directly in BC.

We must do it, so we are able to install and use it for both BC OnPrem and BC SaaS, right?


So, what is this “Print Processor” thingy and what does it run on?

Well, it is something that can obtain a list of available printers and call a BC webservice to tell BC about them. It should also call a BC webservice at some interval to see if there are any print jobs (PDFs) waiting, and if so – fetch the PDFs and print them. For the last part we can simply rely on solutions other created – it cannot be that hard to print a PDF, right?

Ideally the “Print Processor” should be available on multiple platforms, it only has a few requirements:

  • Connect to the network so it can call BC webservices (Internet if BC SaaS)
  • Call webservices and handle the result
  • Connect to the local printer (network, USB etc.)
  • Print a PDF to a local printer

Ideally it could just be some kind of script? Python for Linux and other Unix like systems (some NAS servers even run Unix like systems). And maybe PowerShell for Windows while we are at it?


Really – how hard can it be to pull this off?

Well – https://github.com/DabblerDK/PIPrint

This is in NO WAY final “production” quality software, but feel free to use it on your own costs and risk and build on it.
Release it commercially, use it privately etc.
Please always keep a reference to this blog somewhere people see when they use it. And please give back any improvements to the GitHub repository and the community…

Enjoy…

Running Microsoft SQL Server 2019 Developer Edition in a Docker Windows Container

Note: This work is based on the “Official Microsoft repository for SQL Server in Docker resources” found here: https://github.com/microsoft/mssql-docker/tree/master/windows/mssql-server-windows-developer

Unfortunately this has not been updated to run Windows ServerCore 1890 and Microsoft SQL Server 2019 – so this is what this blog post is about…

Note 2: We are only taking about minor changes here – so I’m not in ANY way trying to claim credits for these scripts – all credits goes to the original author for these scripts – see the above mentioned official link!

Note 3: If you try to use the official Microsoft SQL Server Developer Edition image and you get the error “The container operating system does not match the host operating system.”, it is probably because that image is not Windows Server 1809 compatible. This will also solve that as this image is based on the “mcr.microsoft.com/windows/servercore:1809” Windows Server image.

Enough notes for now :-). Let’s roll with this – first you need to create these two files:

“dockerfile”:

# 2020-05-11 GL
#
FROM mcr.microsoft.com/windows/servercore:1809

LABEL maintainer "Gert Lynge"

# Download Links:
ENV exe "https://download.microsoft.com/download/7/c/1/7c14e92e-bdcb-4f89-b7cf-93543e7112d1/SQLServer2019-DEV-x64-ENU.exe"
ENV box "https://download.microsoft.com/download/7/c/1/7c14e92e-bdcb-4f89-b7cf-93543e7112d1/SQLServer2019-DEV-x64-ENU.box"

ENV sa_password="_" \
    attach_dbs="[]" \
    ACCEPT_EULA="_" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

# make install files accessible
COPY start.ps1 /
WORKDIR /

RUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \
        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \
        Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup ; \
        .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=1 /SQLSVCACCOUNT='NT AUTHORITY\NETWORK SERVICE' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /SQLMAXDOP=1 /SQLBACKUPDIR='C:\Server\MSSQL\Backup' /SQLUSERDBDIR='C:\Server\MSSQL\DB' /SQLUSERDBLOGDIR='C:\Server\MSSQL\DB' ; \
        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup

RUN stop-service MSSQLSERVER ; \
        set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value '' ; \
        set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433 ; \
        set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2 ;

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]

CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

…and “start.ps1”

# 2020-05-11 GL

# The script sets the sa password and start the SQL Service
# Also it attaches additional database from the disk
# The format for attach_dbs

param(
[Parameter(Mandatory=$false)]
[string]$sa_password,

[Parameter(Mandatory=$false)]
[string]$ACCEPT_EULA,

[Parameter(Mandatory=$false)]
[string]$attach_dbs
)

if($ACCEPT_EULA -ne "Y" -And $ACCEPT_EULA -ne "y")
{
	Write-Verbose "ERROR: You must accept the End User License Agreement before this container can start."
	Write-Verbose "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

    exit 1
}

# start the service
Write-Verbose "Starting SQL Server"
start-service MSSQLSERVER

if($sa_password -eq "_") {
    if (Test-Path $env:sa_password_path) {
        $sa_password = Get-Content -Raw $secretPath
    }
    else {
        Write-Verbose "WARN: Using default SA password, secret file not found at: $secretPath"
    }
}

if($sa_password -ne "_")
{
    Write-Verbose "Changing SA login credentials"
    $sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
    & sqlcmd -Q $sqlcmd
}

$attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

$dbs = $attach_dbs_cleaned | ConvertFrom-Json

if ($null -ne $dbs -And $dbs.Length -gt 0)
{
    Write-Verbose "Attaching $($dbs.Length) database(s)"
	    
    Foreach($db in $dbs) 
    {            
        $files = @();
        Foreach($file in $db.dbFiles)
        {
            $files += "(FILENAME = N'$($file)')";           
        }

        $files = $files -join ","
        $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"

        Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
        & sqlcmd -Q $sqlcmd
	}
}

Write-Verbose "Started SQL Server."

$lastCheck = (Get-Date).AddSeconds(-2) 
while ($true) 
{ 
    Get-EventLog -LogName Application -Source "MSSQL*" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message	 
    $lastCheck = Get-Date 
    Start-Sleep -Seconds 2 
}

…and then you have to run these docker commands (replace “<path to a directory with the above mentioned two files>” with the path to the above mentioned two files: dockerfile and start.ps1 – and “<password>” with the sa-password you want):

docker build --memory 4g --tag dabbler/mssql-server-windows-developer:winsrv1809-sql2019 "<path to a directory with the above mentioned two files>" 

docker run --name SQLServer2019 -e ACCEPT_EULA=Y -e sa_password=<password> -p 1433:1433 -d dabbler/mssql-server-windows-developer:winsrv1809-sql2019

If you are lucky, you will now have a running SQL Server 2019 Developer Edition and you can connect directly to it with Microsoft SQL Server Management Studio directly on the host running Docker (use “SQL Server Authentication” with host: “localhost”, login: “sa” and the password stated above).

The task cannot be run because the user account that is assigned to run the task has been altered

Microsoft Dynamics NAV logo

Have you tried to rename a company (from the Companies page or from PowerShell (Rename-NAVCompany cmdlet) in Microsoft Dynamics NAV 2018 and you get this error: “The task cannot be run because the user account that is assigned to run the task has been altered”, you are hit by a side effect of a new Service Tier Setting.

A quick google of the error message yields a few results – especially this one: https://forum.mibuso.com/discussion/72652/job-queue-with-task-scheduler-and-user-id… which suggest that it has something to do with the Service Tier Setting “EnableUserConsistencyValidationOnTasks”.

If we look at Microsoft description of that Service Tier Setting at https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/configure-server-instance, we see this description:

Specifies whether user consistency checks are done on tasks. Setting this to true helps guard against impersonation of users in tasks. When enabled, only the user who created the task can edit the task. If a task is edited by different user account, an error similar to the following occurs: The task cannot be run because the user account that is assigned to run the task has been altered. The task has been canceled.

Default: true

Dynamically Updatable: No

…so if this protection is not important to you – you can easily work around the error by setting this key to false. If this protection is important to you, you should off cause just set it to false while renaming the company and then set it back to true again.

Have a Merry Christmas and a Happy New Year!

Playing around with the DynamicsNAV:// protocol handler

Dynanmics 365 Business Central logo

If you have been using the Dotnet ClickOnce technology to roll out your Dynamics NAV / 365 Business Central Windows Clients, you know it have some limitations (click here to read all about the limitations).

But if you start pulling the ClickOnce technology apart, you will find that the client files are in fact present on your harddrive, but in a obscure directory – which can be hard to locate (actually you can just start the client and go to task manager. Right click the running program and select to open the directory).

What if someone could make a small program or script that would locate all installed ClickOnce Dynamics NAV / 365 Business Central Windows Clients, present you with a list to choose from and then simply start the selected client with the appropriate parameters?

Maybe we can even register this small program or script as the protocol handler for the DynamicsNAV:// protocol?

Well, here is my first attempt of a PowerShell script I call NAVBCProtocolHandlerHelper.ps1 :-):

# Version: 2019-08-06

$SearchPath = "C:\Users\$($env:UserName)\AppData\Local\Apps\2.0\"

$WindowsClientFileName = "Microsoft.Dynamics.Nav.Client.exe"
$ClientUserSettingsFileName = "ClientUserSettings.config"

if(($args[0] -eq "?") -or ($args[0] -eq "-?") -or ($args[0] -eq "-h") -or ($args[0] -eq "-help")) {
  Write-Host "#######################################################################" -ForegroundColor Cyan
  Write-Host "# Microsoft Dynamics NAV/365 Business Central Protocol Handler Helper #" -ForegroundColor Cyan
  Write-Host "#######################################################################" -ForegroundColor Cyan
  Write-Host "#             Copyright 2019, Gert Lynge, www.dabbler.dk              #" -ForegroundColor Cyan
  Write-Host "#######################################################################" -ForegroundColor Cyan
  Write-Host "# This script is free to use, modify and distribute as long as these  #" -ForegroundColor Cyan
  Write-Host "# lines are kept intact. NO WARRANTY! Use at your own risk!           #" -ForegroundColor Cyan
  Write-Host "#######################################################################" -ForegroundColor Cyan
  Write-Host ""
  Write-Host "Examples:" -ForegroundColor Cyan
  Write-Host ""
  Write-Host "Install NAV/365BC Protocol Handler:" -ForegroundColor Cyan
  Write-Host "$($MyInvocation.MyCommand.Definition) -InstallProtocolHandler"
  Write-Host "Be warned: This will override any existing DynamicsNAV:// protocol handler" -ForegroundColor Red
  Write-Host ""
  Write-Host "Remove NAV/365BC Protocol Handler:" -ForegroundColor Cyan
  Write-Host "$($MyInvocation.MyCommand.Definition) -RemoveProtocolHandler"
  Write-Host "Be warned: This will remove any DynamicsNAV:// protocol handler installed" -ForegroundColor Red
  Write-Host ""
  Write-Host "Show list of NAV/365BC ClickOnce Windows Clients and let you select one to start:" -ForegroundColor Cyan
  Write-Host "$($MyInvocation.MyCommand.Definition)"
  Write-Host ""
  Write-Host "Show this help page:" -ForegroundColor Cyan
  Write-Host "$($MyInvocation.MyCommand.Definition) -help"
  exit
}
elseif(($args[0] -eq "InstallProtocolHandler") -or ($args[0] -eq "-InstallProtocolHandler")) {
  $currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
  if(-not $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
    Throw "Installing the DynamicsNAV:// Protocol handler requires this script to be run as Administrator"
  }

  New-PSDrive -Name HKCR -PSProvider Registry -Root "HKEY_CLASSES_ROOT" -ErrorAction SilentlyContinue | Out-Null
  New-Item -Path "HKCR:\DynamicsNAV" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV" -Name "(Default)" -Value "Dynamics NAV Protocol" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV" -Name "URL Protocol" -Force | Out-Null

  New-Item -Path "HKCR:\DynamicsNAV\DefaultIcon" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV\DefaultIcon" -Name "(Default)" | Out-Null

  New-Item -Path "HKCR:\DynamicsNAV\Shell" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV\Shell" -Name "(Default)" | Out-Null

  New-Item -Path "HKCR:\DynamicsNAV\Shell\Open" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV\Shell\Open" -Name "(Default)" | Out-Null

  New-Item -Path "HKCR:\DynamicsNAV\Shell\Open\Command" -Force | Out-Null
  New-ItemProperty -Path "HKCR:\DynamicsNAV\Shell\Open\Command" -Name "(Default)" -Value "PowerShell.exe -WindowStyle Hidden -File ""$($MyInvocation.MyCommand.Definition)"" ""%1""" | Out-Null

  Write-Host "Protocol Handler DynamicsNAV:// installed" -ForegroundColor Green
  exit
}
elseif(($args[0] -eq "RemoveProtocolHandler") -or ($args[0] -eq "-RemoveProtocolHandler")) {
  $currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
  if(-not $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
    Throw "Removing the DynamicsNAV:// Protocol handler requires this script to be run as Administrator"
  }

  New-PSDrive -Name HKCR -PSProvider Registry -Root "HKEY_CLASSES_ROOT" -ErrorAction SilentlyContinue | Out-Null
  Remove-Item -Path "HKCR:\DYNAMICSNAV" -Recurse -Force | Out-Null

  Write-Host "Protocol Handler DynamicsNAV:// removed" -ForegroundColor Green
  exit
}


$WindowsClients = New-Object System.Data.DataTable
$WindowsClients.Columns.Add("Product","System.String") | Out-Null
$WindowsClients.Columns.Add("Server","System.String") | Out-Null
$WindowsClients.Columns.Add("Port","System.String") | Out-Null
$WindowsClients.Columns.Add("Instance","System.String") | Out-Null
$WindowsClients.Columns.Add("Tenant","System.String") | Out-Null
$WindowsClients.Columns.Add("Authentication","System.String") | Out-Null
$WindowsClients.Columns.Add("Version","System.Version") | Out-Null
$WindowsClients.Columns.Add("Path","System.String") | Out-Null

foreach($File in Get-ChildItem -Path "$SearchPath$WindowsClientFileName" -Recurse | Select-Object FullName) {
  $Path = (Split-Path -Path $File.FullName -Parent)

  if(Test-Path "$Path\$ClientUserSettingsFileName" -PathType Leaf) {
    [xml]$XmlDocument = Get-Content -Path "$Path\$ClientUserSettingsFileName"
    $ChildNodes = $XmlDocument.configuration.appSettings.ChildNodes

    $WindowsClient = $WindowsClients.NewRow()
    $WindowsClient.Path = $Path
    $WindowsClient.Version = [System.Version][System.Diagnostics.FileVersionInfo]::GetVersionInfo($File.FullName).FileVersion
    $WindowsClient.Server = ($ChildNodes | Where-Object {($_.key -eq "Server")}).value
    $WindowsClient.Port = ($ChildNodes | Where-Object {($_.key -eq "ClientServicesPort")}).value
    $WindowsClient.Instance = ($ChildNodes | Where-Object {($_.key -eq "ServerInstance")}).value
    $WindowsClient.Tenant = ($ChildNodes | Where-Object {($_.key -eq "TenantId")}).value
    $WindowsClient.Authentication = ($ChildNodes | Where-Object {($_.key -eq "ClientServicesCredentialType")}).value
    $WindowsClient.Product = ($ChildNodes | Where-Object {($_.key -eq "ProductName")}).value
    $WindowsClients.Rows.Add($WindowsClient)
  }
}
$WindowsClients = $WindowsClients | Group-Object -Property Server,ClientServicesPort,ServerInstance,TenantId | `
                  foreach { $_.Group | Sort-Object @{Expression="Version";Descending="$True"} | Select-Object -First 1 }

$Picked = $WindowsClients | Out-GridView -PassThru -Title "Select which Microsoft Dynamics NAV/365 Business Central ClickOnce Windows Client to run"

if(-not $Picked) {
  exit
}

$Command = "$($Picked.Path)\$WindowsClientFileName"
if($args[0]) {
  & "$Command" "-protocolhandler" $args[0]
}
else {
  & "$Command"
}

Please run NAVBCProtocolHandlerHelper.ps1 from a powershell CLI with the -help parameter and read the possibilities. It has five ways of running:

  1. Show the help:
    NAVBCProtocolHandlerHelper.ps1 -help
  2. Install itself as the DynamicsNAV:// protocol handler (OVERWRITING the existing one):
    NAVBCProtocolHandlerHelper.ps1 -InstallProtocolHandler
  3. Remove ANY DynamicsNAV:// protocol handler (also ones the script did not install itself):
    NAVBCProtocolHandlerHelper.ps1 -RemoveProtocolHandler
  4. Show list of installed ClickOnce Windows Clients for Dynamics NAV/365 Business Central and lets you choose one. The chosen one is started:
    NAVBCProtocolHandlerHelper.ps1
  5. Started through the DynamicsNAV:// protocol.
    A list of installed ClickOnce Windows Clients for Dynamics NAV/365 Business Central will be shown. When you chose one, it is started and the URL is passed as a parameter to it (just like if it was called directly from the protocol handler):
    DynamicsNAV://<your parameters>

This is still work in progress and can be improved in many ways. Feel free to send me your comments and wishes.

You can use and modify this free of charge as long as you keep the blocks shown in the help intact.
This is completely without any warranty of any kind – so use at your own risk!
Parameters and functionalities are subject to changes as this is work in progress.

Publish-NAVApp : Extension compilation failed

Dynanmics 365 Business Central logo

Have you ever tried to publish an app-file using the Publish-NavApp PowerShell Cmdlets on a on-premises Microsoft Dynamics 365 Business Central and was stopped by errors like these:

Publish-NAVApp : Extension compilation failed
source/codeunits/Cod13653.PaymentExportManagement.al(4,27): error AL0185: Codeunit 'Payment Export Mgt' is missing
source/codeunits/Cod13656.DataMigration.al(15,28): error AL0185: Codeunit '9002' is missing
source/codeunits/Cod13650.FIKManagement.al(54,24): error AL0185: Table 'Payment Method' is missing
source/codeunits/Cod13650.FIKManagement.al(98,29): error AL0185: Table 'Company Information' is missing
source/codeunits/Cod13650.FIKManagement.al(126,59): error AL0185: Table 'Bank Acc. Reconciliation' is missing
source/codeunits/Cod13650.FIKManagement.al(128,18): error AL0185: Table 'Bank Account' is missing
source/codeunits/Cod13650.FIKManagement.al(129,22): error AL0185: Table 'Data Exch. Def' is missing
source/codeunits/Cod13650.FIKManagement.al(130,26): error AL0185: Table 'Data Exch. Mapping' is missing
[…]
source/codeunits/Cod13657.FIKSubscribers.al(360,46): error AL0132: 'Page' does not contain a definition for 'Payment Application'
 source/codeunits/Cod13657.FIKSubscribers.al(361,56): error AL0185: Page 'Payment Application' is missing
 source/codeunits/Cod13657.FIKSubscribers.al(361,111): error AL0185: Table 'Bank Acc. Reconciliation Line' is missing
 At line:10 char:1
 Publish-NAVApp -ServerInstance $ServerInstance -SkipVerification -Pat …
 ~~~~~~~~~~~~~~~~~ CategoryInfo          : InvalidOperation: (:) [Publish-NAVApp], InvalidOperationException
 FullyQualifiedErrorId : MicrosoftDynamicsNavServer$DynamicsNAV/nav-systemapplication,Microsoft.Dynamics.Nav.Apps.Management.Cmdlets.PublishNavApp 

Note – it seems that all objects are missing!?

It seems illogical, but it is fixed by running symbol generation:

  1. In the Microsoft Dynamics 365 Business Central administrative console you should set the parameter Enable loading application symbol references at server startup in the Development group.
  2. Then you should go to the Program Files (x86) installation folder for the Role Tailored Client (typically C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\<version>\RoleTailored Client) and run this command:
finsql.exe Command=generatesymbolreference, Database="<application sql database>", ServerName=<sql server host name>, ntauthentication=no, username=<sql server username>, password=<sql server password>

Note: The finsql.exe command will start a background finsql.exe process (you can see it in Windows Task Manaer). When it finishes it will write one or two text-files in the Role Tailored Client directory – one with status (filename navcommandresult.txt) and the other one if any errors happens (filename naverrorlog.txt).

These two steps fixed it for me.

You can read more about the symbol generation in the Microsoft documentation by clicking here!

Bad Request – Error in query syntax.

PowerShell logo

No, this is not an error in WordPress on this server – “Bad Request – Error in query syntax.” is in fact the intended subject for this Blog post :-).

If you are running OData WebServices towards you Microsoft Dynamics NAV or 365 Business Central (DBC) and you get this error – and you have a forward slash ( “/” ) in the company name in question, then this Blog post is for you :-).

You have just discovered that Excel, PowerShell.exe and/or older version of PowerShell ISE cannot work with OData URLs with a forward slash in the …/Company(‘<my company name>’)/… part of the URL.

A obvious solution is to rename the company in NAV/DBC – but that is not really a neat solution, is it?

According to this Microsoft blog post, another far superior solution exists. NAV/DBC supports two syntaxes when stating the company name.

The “classic” one you use for OData version 3 when you got the error:

https://<my hostname>:<my odata port>/<my instance>/OData/Company('<my company')/<my webservice>

…or the corresponding OData version 4 one:

https://<my hostname>:<my odata port>/<my instance>/ODataV4/Company('<my company')/<my webservice>

…and another one for working around this error:

https://<my hostname>:<my odata port>/<my instance>/OData/<my webservice>?company='<my company>'

…or the corresponding OData version 4 one:

https://<my hostname>:<my odata port>/<my instance>/ODataV4/<my webservice>?company='<my company>'

Note that you can off cause add additonal parameters (selecting tenant or setting filters etc.) to the URL using the ampersand sign ( “&” ) – just like you do when construction any URL.

Congratulations – you solved your problem πŸ™‚

If you want to learn more about OData towards NAV/DBC, I suggest you visit these links:

Cent and Yen in Dynamics NAV 2018

Microsoft Dynamics NAV logo

If you run a Danish Dynamics NAV and get objects from outside Denmark, you might experience the Danish letter ΓΈ/Ø beeing replaced by Β’/Β₯ (the sign for Cent and Yen).

The fix?
Simply export all your objects as text, run them through the following powershell and import them again.

(((Get-Content -encoding Oem "InFile.txt") -replace ([char]8250),([char]251)) -replace ([char]157),([char]238)) | Set-Content -encoding Oem "OutFile.txt"

 

Finish off by a compile and syncronize all tables.

This is off cause on your own risk, so start by taking a full backup of your NAV system/server.


2018-12-20 Updated: powershell improved and now using unicode values of chars

My Dynamics NAV App is FUBAR (updated 2018-06-22 with new solution)

Microsoft Dynamics NAV logo

For readers not knowing what FUBAR is, please see Wikipedia.

If you Dynamics NAV 2018 service will not start (or actually shuts down immediately when you try to start it), and you get something like this in your Windows Event Viewer (App Name, object ID and/or Name can probably be different ones):

Message: <ii>An error occurred while applying changes from the 'Payment and Reconciliation Formats (DK) by Microsoft 1.0.20348.0' app to the application object of type 'PageExtension' with the ID '13623'. The error was: InvalidOperationException - Metadata delta application failed due to the following error(s): The metadata object IncomingDocAttachFile was not found.</ii>

…your installation is FUBAR (or at least I don’t know how to repair it easily).

PowerShell cannot uninstall/unpublish the App when no Service Tier is running – so that is not an option.

Also I was not able to Google a solution, but here is what I did to make the service tier run again:

  • Take a fresh backup, and make sure nobody will open NAV until you say OK (it will be possible as soon as we get the Service Tier running again).
  • Export all your objects to text and locate (search for) the (probably page) object with the metadata object that could not be found (i.e. “IncomingDocAttachFile” in my example). I found it in Page Object 25.
  • Export the (Page) Object to a FOB-file (just so you have it).
    Overwrite/reimport the (Page) Object from a backup without your latest changes (be warned – if this is a table object, this will empty the table – so make sure to preserve the data somehow).
  • Redo your changes to the object without messing with theΒ metadata object that could not be found (i.e. “IncomingDocAttachFile” in my example.

Test that everything is ok, before letting anyone work again.

NOTE: I’ve not tried this with a table object, only a page object – so I’m not even sure this error can occur on tables?

DISCLAIMER: This is probably unsupported by Microsoft and you should do this on your own risk. We will not be responsible for any problems, data loss or down time etc. you may discover/experience following this procedure, or after doing this procedure. You are on your own here – but hopefully this will help you and save you the effort of finding a solution yourself! πŸ™‚


2018-06-22 I did’nt quite nail the solution first time around, as the problem creped in again. So I change the solution to the right one πŸ™‚

Annoying non-rotating picture frame

Big Ben upside-down

If you got a cheap Chinese Picture Frame, you have probably noticed that half your pictures are upside-down, or rotated left or right.

It happens even if the pictures are shown correctly on your computer, so if you got loads of pictures on your frame, it can be quite a hassle to figure out which files are orientated wrong.

The cause of this is the EXIF attribute which stores the correct orientation of the picture. Or rather that most Picture Frames ignores this attribute, probably because they do not have the CPU power to rotate the picture anyway.

The easiest fix is to run this PowerShell script on all your pictures – it will read the EXIF attribute and actually rotate the picture so your Picture frame does not have to.

Have fun!


2020-04-11: Updated: Now the script also resizes images (while maintaining aspect ratio) so your huge images won’t fill up your picture frame (remember to keep a copy of the images if you need them in full resolution)

# 20200411 Gert Lynge. Source: http://www.dabbler.dk
#
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") > $null

function Get-EXIFRotationAttribute($FilePath) {
# Returns:
# 1 = "Horizontal"
# 3 = "Rotate 180 degrees"
# 6 = "Rotate 90 degrees clockwise"
# 8 = "Rotate 270 degrees clockwise"
  $WiaImage = New-Object -ComObject Wia.ImageFile
  $WiaImage.LoadFile($FilePath)
  if($WiaImage.Properties.Exists("274")) {
    return ($WiaImage.Properties.Item("274").Value)
  }
  return -1
}

function RotateClockwise-JpegImage($Degrees,$FilePath) {
  $Image = [System.Drawing.image]::FromFile( $FilePath )
  switch($Degrees) {
    90  { $Image.rotateflip("Rotate90FlipNone") }
    180 { $Image.rotateflip("Rotate180FlipNone") }
    270 { $Image.rotateflip("Rotate270FlipNone") }
  }
  $Image.save($FilePath)
  $Image.Dispose()
  Write-Host -ForegroundColor Yellow "$FilePath rotated $Degrees degrees clockwise"
}

function ResizeImage() {
    param([String]$ImagePath, [Int]$Quality = 90, [Int]$targetSize, [String]$OutputLocation)
 
    Add-Type -AssemblyName "System.Drawing"
 
    $img = [System.Drawing.Image]::FromFile($ImagePath)
 
    $CanvasWidth = $targetSize
    $CanvasHeight = $targetSize
 
    #Encoder parameter for image quality
    $ImageEncoder = [System.Drawing.Imaging.Encoder]::Quality
    $encoderParams = New-Object System.Drawing.Imaging.EncoderParameters(1)
    $encoderParams.Param[0] = New-Object System.Drawing.Imaging.EncoderParameter($ImageEncoder, $Quality)
 
    # get codec
    $Codec = [System.Drawing.Imaging.ImageCodecInfo]::GetImageEncoders() | Where {$_.MimeType -eq 'image/jpeg'}
 
    #compute the final ratio to use
    $ratioX = $CanvasWidth / $img.Width;
    $ratioY = $CanvasHeight / $img.Height;
 
    $ratio = $ratioY
    if ($ratioX -le $ratioY) {
        $ratio = $ratioX
    }

    if ($ratio -ne 1) { 
      $newWidth = [int] ($img.Width * $ratio)
      $newHeight = [int] ($img.Height * $ratio)
 
      $bmpResized = New-Object System.Drawing.Bitmap($newWidth, $newHeight)
      $graph = [System.Drawing.Graphics]::FromImage($bmpResized)
      $graph.InterpolationMode = [System.Drawing.Drawing2D.InterpolationMode]::HighQualityBicubic
 
      $graph.Clear([System.Drawing.Color]::White)
      $graph.DrawImage($img, 0, 0, $newWidth, $newHeight)
 
      #save to file
      $img.Dispose()
      $bmpResized.Save($OutputLocation, $Codec, $($encoderParams))
      $bmpResized.Dispose()
      
      Write-Host -ForegroundColor Yellow "$FilePath scaled to $newWidth * $newHeight"
    }
}

function Rotate-JpegImages($ImagePath) {
  write-host -ForegroundColor Yellow "Checking for jpg/jpeg images that needs rotation in path $ImagePath (including subdirectories)..."
  $JpegFiles = get-childitem -Recurse -Path "$ImagePath\*" -File -Include @("*.jpg","*.jpeg")
  ForEach($JpegFile IN $JpegFiles) {
    $FilePath = Join-Path -Path $ImagePath -ChildPath $JpegFile.Name

    $EXIFRotationAttribute = Get-EXIFRotationAttribute $FilePath
    switch($EXIFRotationAttribute) {
      6 { RotateClockwise-JpegImage 90 $FilePath }
      3 { RotateClockwise-JpegImage 180 $FilePath }
      8 { RotateClockwise-JpegImage 270 $FilePath }
    }

    ResizeImage -ImagePath $FilePath -targetSize 1280 -OutputLocation $FilePath

  }
}

# Rotate all images on D: (including files in subdirectories)
clear
Rotate-JpegImages "D:\"