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.

NAV 2013 R2 Unable to verify table changes

In my setup I am running a Developement Environment on one computer, the Server on another and the database is on the third computer – the SQL Server.

Running Server and Database on separate machines requires a domain user account for database authentication.  What I have found is that a service running on a domain user account is unable to verify table changes.

ServerCannotBeReached

The server is running.  I can see the server instance.

AvailableServerInstances

There are two methods to get around this error.

  • Start a Server Instance on your Developement machine using domain user account
  • Start a Server Instance on your SQL server using Network Service

Using OpenXML to create an Excel Document

In one of my projects I needed to create a complex Excel document from Dynamics NAV.  I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370.  The problem is that this is dead slow.  It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document.  Great blog and a big help.  The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”.  I was inserting an invalid character into the XML.  I did a quick search and found a solution, I needed to filter the data through a white list.  Here is my AddCell function:

    LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
    VAR
      XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
      CellValueAsText@1200050007 : Text[1024];
      CommentLine@1200050003 : Text[1024];
      CommentPosition@1200050004 : Integer;
      CommentLength@1200050005 : Integer;
    BEGIN
      IF CurrentRow = 0 THEN
        GlobalRowNo := GlobalRowNo + 1
      ELSE
        GlobalRowNo := CurrentRow;
      IF CurrentCol = 0 THEN
        GlobalColumnNo := GlobalColumnNo + 1
      ELSE
        GlobalColumnNo := CurrentCol;

      //Comments are not yet supported by ClosedXML
      //CommentLength := CommentText.LENGTH;
      //IF CommentLength > 0 THEN BEGIN
      //  CommentPosition := 1;
      //  WHILE CommentPosition < CommentLength DO BEGIN
      //    CommentPosition := CommentPosition  + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
      //    XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
      //  END;
      //END;

      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
      IF IsUnderline THEN
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
          GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

      IF Attention THEN BEGIN
        XlColor := XlColor.FromName('Red');
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
      END;

      IF IsFormula THEN
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
      ELSE
        CASE TRUE OF
          Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
            BEGIN
              CellValueAsText := FORMAT(Value);
              CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
              XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
            END;
          ELSE IF FORMAT(Value) <> '0' THEN
            XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
        END;

      CASE Alignment OF
        Alignment::General:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
        Alignment::Center:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
        Alignment::Right:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
        Alignment::Left:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
      END;
    END;

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

is used to clean the string that is passed to the XML.

The GetEnumValue function is

    LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
    VAR
      Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
    BEGIN
      ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
    END;

When constructing the OpenXML dotnet object I also construct the white characther check object.

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in

 

 

VB.NET NAV Application Server

Most of my clients require a running NAV Application Server.  The NAS that is included in NAV 2009 R2 requires a license that is included in most licenses today.  However, there are cases where more than one NAS is needed.  That requires additional NAS licenses.  Where the customer is running NAV 2009 R2 middle tier service this changes.  By running a VB.NET NAV Application Server it is possible to setup multiple services on a single CAL license.  The CAL license is not as expensive as the NAS license.  Here is the solution that I offer.

First, I create a codeunit in NAV

ExecuteCodeunit(CodeunitID : Integer;Log : Boolean) Success : Boolean

IF Log THEN LogEntryNo := InsertLogEntry(5,CodeunitID);
Success := CODEUNIT.RUN(CodeunitID);
IF Log THEN
  UpdateLogEntry(LogEntryNo,Success)
ELSE IF NOT Success THEN BEGIN
  LogEntryNo := InsertLogEntry(5,CodeunitID);
  UpdateLogEntry(LogEntryNo,Success)
END;

InsertLogEntry(ObjectType : ',,,Report,,Codeunit';ObjectNo : Integer) : Integer
WITH JobQueueLogEntry DO BEGIN
  INIT;
  ID := CREATEGUID;
  "User ID" := USERID;
  "Start Date/Time" := CURRENTDATETIME;
  "Object Type to Run" := ObjectType;
  "Object ID to Run" :=  ObjectNo;
  INSERT(TRUE);
  COMMIT;
  EXIT("Entry No.");
END;

UpdateLogEntry(LogEntryNo : Integer;WasSuccess : Boolean)
WITH JobQueueLogEntry DO BEGIN
  GET(LogEntryNo);
  "End Date/Time" := CURRENTDATETIME;
  IF WasSuccess THEN
    Status := Status::Success
  ELSE BEGIN
    Status := Status::Error;
    SetErrorMessage(COPYSTR(GETLASTERRORTEXT,1,1000));
  END;
  MODIFY;
  COMMIT;
END;

This codeunit uses the Job Queue Log to log the execution.  Next step is to publish this codeunit as a web service in table no. 2000000076.  Default web service name is NAVAppServer.

On the server you install the following files (in my case to C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server)


Next step is to edit the VB.NET NAV Application Server.exe.config file and customize the values.

 <applicationSettings>
        <NAV_Application_Server.My.MySettings>
            <setting name="NAVAppServer" serializeAs="String">
                <value>http://<Middle Tiere Host Name>:7047/DynamicsNAV/WS/<CompanyName>/Codeunit/NAVAppServer</value>
            </setting>
            <setting name="CodeunitID" serializeAs="String">
                <value>81004</value>
            </setting>
            <setting name="LogMode" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="TimerInterval" serializeAs="String">
                <value>90000</value>
            </setting>
            <setting name="LogFrequency" serializeAs="String">
                <value>15:00:00</value>
            </setting>
            <setting name="SMTPHost" serializeAs="String">
                <value>mail.dynamics.is</value>
            </setting>
            <setting name="FromAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="ToAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="RetryTimerInterval" serializeAs="String">
                <value>45000</value>
            </setting>
        </NAV_Application_Server.My.MySettings>
    </applicationSettings>

To install as a service start command prompt in elevated mode and execute installutil.exe command.  The install will prompt for the user to start the service.

C:\Windows\Microsoft.NET\Framework\v2.0.50727>InstallUtil.exe "C:\Program Files(x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe"

Microsoft (R) .NET Framework Installation utility Version 2.0.50727.5420
Copyright (c) Microsoft Corporation.  All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Installing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog
Installing service VB.NET-NAVAppServer$DEFAULT...
Service VB.NET-NAVAppServer$DEFAULT has been successfully installed.
Creating EventLog source VB.NET-NAVAppServer$DEFAULT in log Application...

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Committing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

The last step is to change the service startup and start the service.

The service will create entries in the Application Log.

NAV Web Service Codeunit

The following ZIP files are encrypted.

VB.NET NAV Application Server DEFAULT Executables

NAV App Server Visual Studio 2008 Project

When the Database Name has non-english character

At a client I was trying to start the NAV 2009 R2 Service Tier.  The client has a non-english character in the database name and the service tier complained and would not start.  This is an issue that Microsoft fixed recently and after I installed a newer version of the service tier everything works fine.

I also had to make sure that the CustomSettings.config file was saved with UTF-8 encoding.  This can be done by running Notepad in administrative mode, open the file and select the UFT-8 encoding in save-as dialog.