March 2016 – Gunnar's Blog

March 31, 2016

Presenting the Data Exchange Framework

Earlier this month Arend-Jan contacted me about being a presenter on Dutch Dynamics Community NAV Event for March 2016. Of course I was honored and after a moments thought I accepted.

The following presentation was repeated two times for close to 140 people in total. I had a great time and am thankful for the opportunity.

I promised to share one Codeunit – that general mapping Codeunit – and here it is: Codeunit 60000.

If you download the presentation you can also read my notes. They should help you better understand the whole story.

March 26, 2016

Data Exchange Framework enhancements

I have made several enhancements to the Data Exchange Framework in NAV 2016. Previously I wrote about text or a comma separated file import and about access to a local data file when creating a xml/json structure. Both those enhancements are included in the objects attached to this post.

I have needed to handle JSON files where the actual data is not in the node value but in the node name. To support this I added a boolean field to the Data Exchange Column Definition table (1223).

To support this I added the same field to the Data Exchange Field Mapping table (1225) as a flow field, calculating the value from the definition table.

Codeunit 1203 is used to import XML and JSON files into the Data Exchange Field table. I made two changes to the InsertColumn. First, I added a parameter that accepts the node name and if the above switch is set to true I insert the node name into the column value instead of the node value. The other change is that instead of finding only the first Data Exchange Column Definition I loop through all of them. This allows me to create more than one definition based of the same XML node and therefore import into multiple columns from the same node.

To enable this scenario in the Currency Exchange Update Service I made changes to the Data Exchange Field Mapping Buffer table (1265) and the Data Exchange Setup Subform page. More on that later.

A JSON file without a single root element will cause an error in the standard code. Inspired my Nikola Kukrika on NAVTechDays 2015 I made changes to Codeunit 1237 utilizing the TryFunction to solve this problem. To top this of I made a simple change to the Currency Exchange Rate Service Card page (1651) and table (1650) to allow JSON file type.

Having completed these changes I can now use a web service that delivers JSON in my Currency Exchange Rate Service.

Also inspired by Microsoft I added a transformation type to the Transformation Rule table (1237) to convert the Unix Timestamp (seconds since January 1st 1970 UTC) to a date.

All objects and deltas are attached

DEF-Enhancements

March 17, 2016

Asynchronous web services in NAV

Asynchronous methods are very useful for web services. To be able to start a process with one method and then check the status with another opens a lot of possibilities.

There are two known patterns in NAV that support asynchronous methods; one is to use the STARTSESSION function to execute the business logic in another thread, the other is to create a job queue entry and let NAS handle the task.

I was talking to a client this morning and suggested using the asynchronous web services for his tasks. I decided to write some code and test the functionality to see if my theory was working.

The first thing I need is a table for the requests. This table contains the identification for the queue, the status and other details.

A single web service method is used to create a new queue entry.

I like to use the new TryFunction for my web services. Note that when the queue has been inserted I fire an event with the newly created queue entry. This means that I can extend this web service with events without having to modify the web service signature.

Each process needs a dedicated Codeunit. That Codeunit is built on top of the queue record and thereby using the parameter table pattern.

Note that this Codeunit is also using the TryFunction and taking care of the rollback if that function fails.

The process Codeunit catches the integration event and checks the process code before starting then selected task.

So, if I ask the web service to start a job called StartAsyncAdjustCostItemEntriesProcess, NAV will start a new session to execute the adjust cost for item entries. The process that creates the queue and starts a new session is very quick so the response from the web service is almost instant.

Then we wait for a moment and ask the web service about the status for the newly created queue.

Again using the TryFunction to make sure that my web service will have a proper response to the query.

It is quite easy to extend this module by catching the OnNewQueueInserted event for each process code you build support for.

A sample C# code that I used to test this looks like this

As you can imagine we can put anything into the ProcessData variable. By using Base64 encoding anything can be converted to a text variable.

I hope this will turn on some lights and you will be able to use this in your daily work.

Attached is a zip file with the NAV objects and the C# project.

NAVAsyncWebService

March 15, 2016

NAVUG European Congress

This conference, being held 9-10 May 2016 in Stuttgart, Germany, is a user-driven event dedicated to helping Dynamics NAV users maximize the return on their Dynamics investment. The programme schedule currently include the following sessions and more:

Why the Cloud? Options and Considerations for Full or Hybrid Hosting
Approaches to Managing Your Historical Data
Best Practices for Dynamics NAV Administration and Security
Power BI in Action
Global vs. Local: Localizations and Cultural Aspects

Experienced Dynamics users and partners will lead instructional sessions, showcases, and How2’s, focusing on past and current versions of Dynamics NAV. As an attendee you will:

Broaden your Dynamics NAV product knowledge through hands-on training sessions, prepared by knowledgeable Dynamics NAV end users and partners.
Improve your Dynamics NAV competency to be more efficient in the workplace.
Learn more about the Dynamics NAV version you are currently using; as well as newer versions if you are looking to upgrade.
Network with other Dynamics NAV peers who use the product on a daily basis.

The conference will be held at the International Congress Centre in Stuttgart, Germany on 9-10 May 2016. To learn more about the conference or to register today, please visit www.navugcongress.com.

March 9, 2016

My SQL Server 2014 gets flooded and stops responding

All right, I must confess; my SQL Server is not installed on a super computer. Also, it is not installed and configured by a SQL Server MVP nor by Alain Krikilion (picture from NAV TechDays 2013).

The Hyper-V machine running my SQL Server is using two processors and 12GB of memory. Operating system is Windows Server 2012 R2 x64 and SQL Server version 2014.

I have two NAV 2016 CU5 instances on two servers running a multi tenant application. Every time I started the second instance everything froze. Even the SQL Server stopped responding. I am not going to pretend that I understand the issue, but still, I found a solution and wanted to share that with you.

We have a property for the NAV Service called “Max Concurrent Calls”. This is, by default, set to 40. I found out that if I lowered this value to 10 on the second instance I was able to start it. That is a workaround, not a solution. What if I want to start the third service instance, do I then need to lower that even more or update the configuration for all the previously running instances?

So I turned my attention to my SQL Server. On MSDN Microsoft states:

This topic describes how to set the user connections server configuration option in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that the maximum (32,767) user connections are allowed.

This page also shows an example on how to configure this setting. In that example the property is set to 325. So, I tried that, returning the “Max Concurrent Calls” back to the default value for all NAV Services. Happy times, everything is running smooth.

So how can two NAV Servers that have “Max Concurrent Calls” set to 40 flood a SQL server that supports 325 concurrent user connections? I even looked at the network connections by executing

netstat -nao | find “1433” > connections.txt

in command prompt and looking at the output. The total number of connections was less than 50! I think I must leave this to Microsoft or my friend Alain to explain this.

So it was clear; the Next, Next, Finish methodology failed me this time. I needed to get my hands dirty and fix the SQL Server configuration.

First, I looked at the “Max Degree of Parallelism” property. The default value is zero but is should be set to “No. of CPUs” – 1.

Then updated the connections property.

I restarted the SQL Server Service to apply changes.

I updated one more property and below is the SQL Query I used to update these properties.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max degree of parallelism', 1 ;
EXEC sp_configure 'user connections', 512 ;
EXEC sp_configure 'max worker threads', 2048 ;
GO
RECONFIGURE;
GO

Why this turns up in NAV 2016 and not in previous NAV versions, what changed, I don’t know. Perhaps someone out there has the answer.

If you get stuck in a problem like this, see if you have NAS services running on both instances. That seemed to be my problem.

March 7, 2016

Significant hotfix in 2016 CU5 – if you use Events

Microsoft just published Cumulative Update 5 for NAV 2016.

What caught my attention is the highlighted fix.

So happens that this very morning I was going to use this functionality and nothing happened. Looked at my subscriber and everything seemed to be as it should be.

I guess I will need to apply this hotfix and see what happens.

March 7, 2016

Impossible to debug the Job Queue in NAV 2016?

I guess I am not the only one that has installed NAV 2016 running the Job Queue. If the Job Queue is running a job that causes error you are likely to start the debugger on the service tier running the Job Queue and start with a Debug Next.

You can expect a break point with an error similar to this one.

Well, not what you where hoping for. Not the job you where planning to debug, right. Well, you try again and get the same error, never getting to the error you wanted to debug in the first place. And why is that?

Microsoft decided to utilize the TryFunction in the Job Queue

So, each time JobQueueEntry.FINDFIRST fails the debugger breaks. So I asked Microsoft, why change this from being an normal function containing EXIT(JobQueueEntry.FINDFIRST). The response was:

“We wrapped the FINDFIRST in a TRY-function because we regularly got (dead-)lock errors when we had multiple queues running at the same time, and this fix has virtually eliminated that problem. And since we cannot have return values from try-functions, we would have to pass on a boolean VAR parameter to indicate if it was found or not.”

Ok, so there is a reason, and a good one.

So I started a little ping pong with Microsoft and we agreed that this should be fixed. A new local function was added to Codeunit 448 and the TryFunction was modified. The previous call to a TryFunction was modified to call the new local function instead.

So, both problems fixed; (dead-)lock not causing the Job Queue to stop and we are not getting troublesome break points in the debugger.

Attached are Delta, FOB and TXT versions for this fix.

COD448