CALCFIELDS and BLOB in newer NAV versions

Just wanted to put this out there.

In older NAV versions you needed CALCFIELDS on a BLOB field before HASVALUE.  Now you can ask for HASVALUE before CALCFIELDS.

Having unsaved data in a BLOB field and doing CALCFIELDS will try to get the saved data into the BLOB field.  Try a code like this:

[code]
TempBlob.INIT;
IF FileMgt.BLOBImport(TempBlob,’Image.png’) = ” THEN EXIT;
Storage.Blob := TempBlob.Blob;
MESSAGE(‘Blob has value: %1’,Storage.HASVALUE);
Storage.CALCFIELDS(Blob);
MESSAGE(‘Blob has value: %1’,Storage.HASVALUE);
[/code]

This CALCFIELDS will remove the data from the BLOB field.

Doing an INSERT or MODIFY before CALCFIELDS will fix the error, or simply remove the CALCFIELDS line.

JSON meets NAV

I have been using SOAP services over the last years.  Only recently the RESTful web services have become more and more popular in my integration work.  Wikipedia says:

In computing, Representational State Transfer (REST) is a software architecture style for building scalable web services. REST gives a coordinated set of constraints to the design of components in a distributed hypermedia system that can lead to a higher performing and more maintainable architecture.

RESTful systems typically, but not always, communicate over the Hypertext Transfer Protocol with the same HTTP verbs (GET, POST, PUT, DELETE, etc.) which web browsers use to retrieve web pages and to send data to remote servers. REST interfaces usually involve collections of resources with identifiers, for example /people/paul, which can be operated upon using standard verbs, such as DELETE /people/paul.

As we are used to XML as the body for our SOAP messages we can also use XML as the body for a RESTful web service.  I just finished writing a code to communicate with Azure from NAV.  This communication was using RESTful web services and XML.

So, what is JSON?  Wikipedia says:

JSON, (canonically pronounced /ˈdʒeɪsən/ JAY-sən; sometimes JavaScript Object Notation), is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the primary data format used for asynchronous browser/server communication (AJAJ), largely replacing XML (used by AJAX).

Although originally derived from the JavaScript scripting language, JSON is a language-independent data format. Code for parsing and generating JSON data is readily available in many programming languages.

The JSON format was originally specified by Douglas Crockford. It is currently described by two competing standards, RFC 7159 and ECMA-404. The ECMA standard is minimal, describing only the allowed grammar syntax, whereas the RFC also provides some semantic and security considerations. The official Internet media type for JSON is application/json. The JSON filename extension is .json.

With JSON it is possible to deliver similar data structure as with XML.  JSON on the other hand requires a much less metadata.  Here is an example JSON from Wikipedia:

[code lang=”javascript”]{
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "office",
"number": "646 555-4567"
}
],
"children": [],
"spouse": null
}[/code]

There is not a good support for JSON in native .NET from Microsoft.  However, with Visual Studio, Microsoft installs an external DLL in to the folder “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies”

Newtonsoft

With this Json.NET in Dynamics NAV Add-ins folder we now have some way to handle JSON files.  Using this Add-in I created a NAV Codeunit to manage JSON text.

This Codeunit contains functions to build a JSON document, like

[code lang=”csharp”]
StartJSon;
AddToJSon(‘newssn’,CompanyInformation."Registration No.");
AddToJSon(‘billtossn’,BillToCustNo);
AddToJSon(‘newcompanyname’,CompanyInformation.Name);
AddToJSon(‘newemail’,CompanyInformation."E-Mail");
AddToJSon(‘register_einvoice’,EInvoiceEnabled);
AddToJSon(‘register_supdoc’,SupDocEnabled);
AddToJSon(‘register_natreg’,NRLookupEnabled);
EndJSon;
Json := Json.Copy(GetJSon);[/code]

A function to import values from a JSON document to a temporary table, like

[code lang=”csharp”]
ReadJSon(String,TempPostingExchField);

WITH TempPostingExchField DO BEGIN
SETCURRENTKEY("Line No.","Column No.");
IF FIND(‘-‘) THEN REPEAT
SETRANGE("Column No.","Column No.");
InsertFileDetails(TempPostingExchField,WebServiceURL);
FINDLAST;
SETRANGE("Column No.");
UNTIL NEXT = 0;
END;[/code]

Or just a simple way to return a single value from a simple JSON string, like

[code lang=”csharp”]FileName := GetValueFromJsonString(String,’filename’);[/code]

With these functions NAV should be able to handle JSON files without any problems.

Now you can add JSON handling to your arsenal.

Json Codeunit and required add-ins

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.
servicedisabled

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"
}
[/code]

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
[/code]

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://mynavserver.dynamics.is:7046/CRONUS/Service’ 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">
<allowAccounts>
<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" />
</allowAccounts>
</net.tcp>
<net.pipe maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10">
<allowAccounts>
<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" />
</allowAccounts>
</net.pipe>
<diagnostics performanceCountersEnabled="true" />
</system.serviceModel.activation>’

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

$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
$addNode.Attributes.Append($secIden)

$nettcp.AppendChild($addNode)
$xmlDoc.Save($SMSvcHostPathConfig)
Write-Host "Configuration Updated"
#Restart Service if running
if ((Get-Service NetTcpPortSharing).Status -eq "Running") {Restart-Service NetTcpPortSharing -Force}
}

[/code]

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;

Gunnar,
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

}
else
{
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
}
[/code]

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

 

Sellfy.com – get into it…

I have been using Sellfy.com for some time now.  This has forced me to take some of my smaller solutions and turn them into a product.

I connect my Sellfy user to PayPal and give them information about my location.  That is it, all you need to do.  They are supporting the new VAT regulation in between EU countries.

To make things easier to find I created the web site Objects4NAV.com.  There I have a product catalogue with download links.  All download links point to a source outside of Objects4NAV web site.  Some to MiBuSo, some to the source blog post and some to the Sellfy product purchase button.

When a product is made available on Sellfy a price tag has to be applied.  That is normal, this is a store.  The price can be as low as €0.99 so this should not stop anyone.

There is also another great thing about using Sellfy.  If you update your product you can use Sellfy to send the updated product to the ones that bought the product even if you don’t have any information about the purchasers.

Step 1; update the product file.

replaceproductfile

Step 2; send a notification email.

SendUpdate

Step 3; sit back and smile

eMailSent

This example is where I just sent an upgrade to the tool I use to create temporary tables and upgrade codeunit for NAV 2015.  I must say that I am a bit surprise how few have downloaded this product.  It will save so much time in a data upgrade.  Are you not upgrading NAV installations ???

Another example could be a currency import from xe.com.  I posted a function to do this for NAV 2009 R2 and was recently asked for the NAV 2013 R2 version.  I don’t have that in my product list but would it not be nice to be able to find that solution on Objects4NAV.com and download for just €9.  That would be a win-win game.

Technical upgrade from NAV 2013 R2 to NAV 2015

I just made a backup of a live NAV 2013 R2 database to do an upgrade to NAV 2015.  Since the system is live we have several servers running when the backup is created.

The backup was restored in a new environment and the database opened with a NAV 2015 Developement Client.  The database is put in single user mode and converted.  After the conversion a process to upgrade objects starts.  Here is where things start to behave in an unwanted way.

chooseInstance

The database is in a Single User Mode since it is still in the upgrade transaction and no NAV Server instance is connected.  However, in the table “Server Instance” all the old NAV Server instances are listed.  To get through this I needed to press N like a thousand times…

In a test upgrade process like this we need to add another step to the usual upgrade procedure.  After the restore is completed make sure that the tables “Server Instance” and “Active Session” are empty.

After a restore to a new environment it is also good to clean unneeded users from the database.  Only leave what is actually needed.

users

In this case the NAVLIGHT\srvNAV is the service user.  Also make sure that the Compatibility Level is as new as possible and the Recovery Model in line with the backup process.

dbproperty

Also, when moving from one environment to another the users connected to the old domain are obsolete.  Going to Users and trying to remove them results in an error and the user is only disabled.

userdelete

In some cases this could be enough.  It is possible to completely remove the user, just do some housekeeping first.  Find the user in “User Personalization” and Edit the record.  On the Action ribbon we can clean some of the user trails.

clearpersonalization

Finally make sure that the record is deleted from “User Personalization”.

After this cleanup work the user can be safely deleted.

 

Waiting for a server warmup – no more

One of the problems NAV users face is that the client is to slow after the NAV service starts.

The reason for this is that NAV uses just-in-time compilation of the source code.  The source code is compiled on the server when the server needs to use it.

If you want the server to be warm when the demoing NAV you need to make sure that the server compiles all needed code before showing off.

On the Microsoft Dynamics NAV 2015 Azure Demo Template you can see that Microsoft has added a WarmUp script to take care of this problem.  This solution includes a solution that will open a list of pages with the web client to make sure that the server compiles the basic functionality for the demo.

If you want to play with this yourself on your own installation just download the zipped warmup folder (WarmupNAV) and start investigating.