Microsoft Dynamics NAV Sustained Engineering Team Blog

Final Posting on the NAV Sustained Engineering Team Blog

We’re combining the NAV Sustained Engineering Blog with the NAV Team Blog, and will no longer be posting on the SE blog. You can follow our future posts on the NAV Team Blog. Also, the posts from the SE blog have been copied to the NAV team blog.

Setting Up a Mail Template for Document Approval Notifications

The email notifications that are sent between users according to the document approvals setup are based on email templates defining which fields and text to show in the notification.

 

The email template is an HTML file that you can export from Microsoft Dynamics NAV, edit in Word, for example, and then import back to the program where it then defines the content of approval notifications sent between users in the database.

 

The following default notification email template is shipped with the standard product:

 

 

 

 

 

 

 

 

 

The parameters represent the following variables when used for sales approval notifications:

 

 

 

 

 

 

 

 

 

 

 

The parameters represent the following variables when used for purchase approval notifications:

 

 

 

 

 

 

 

 

 

 

 

A sales approval notification based on the default template looks approximately as follows:

 

 

 

 

 

 

 

 

 

 

 

 

Note: The program automatically inserts variables in the notification according to the approval action and document type that it notifies about.    

 

If the default mail template for approval notifications does not fit your needs, you can export, edit, and import it as described in the following procedure.

 

To Set Up a Mail Template for Document Approval Notifications

 

Begin by exporting the default mail template to a folder on your computer.

 

1.      From the navigation pane, click Administration, and then click Application Setup

2.      Click Document Approval, and then click Approval Setup.

3.      In the Approval Setup window, click Mail Templates, point to Approval Mail Template, and then click Export.

4.      Give the HTML file a name, such as “notification mail.htm”, and save it in any folder.

 

Proceed to edit the template to define which fields to include in approval notifications in your company.

 

1.      Locate the exported template file.

2.      Right-click on the file, point to Open with, and then click Word (or Note Pad).

3.      Edit the template by adding, changing, or removing variables to define the notification content you want.

4.      Save and close the HTML file.

 

Finish by importing the changed template.

 

1.      From the navigation pane, click Administration, and then click Application Setup

2.      Click Document Approval, and then click Approval Setup.

3.      In the Approval Setup window, click Mail Templates, point to Approval Mail Template, and then click Import.

4.      Select the HTML file you edited in the previous steps, and then click OK.

5.      Click Yes to overwrite the existing mail template in the database.

 

Overdue Mail Template

 

To define the content of reminder emails to users concerning overdue approval actions, follow the same procedure as for notification mails, but begin by clicking Mail Templates, and then point to Overdue Mail Template in the Approvals Setup window.

Disable UAC if you are doing a technical upgrade from the same machine where SQL is hosted.

When doing a technical upgrade running Microsoft Dynamics NAV CSIDE client on the same machine as your SQL server, please run the CSIDE client with either UAC turned off or with full administrator rights (Run as administrator).

The reason why this is important is because, when connecting locally from a Vista/Windows 2008 server to SQL Server, it will strip down the Windows token from, among other things, honoring builtin\Administrator membership, unless connecting from an elevated command (run as administrator).

This is especially important when doing technical upgrades for NAV 2009 SP1.

This should not be needed if you connect from a remote workstation to SQL server.

 

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

How do Record Links encode their data in SQL server?

Recently I was asked about the encoding that it is done for record links in SQL.

If you are not familiar with Record Links, which have been available since NAV version 5.0, just open any Card or List and then click Edit -> Links (Ctrl + L).

“Record Links enable users to add links to documents to any record in Microsoft Dynamics NAV, such as a sales order or purchase order. The document or order can be stored in Microsoft SharePoint or on a file server and the user can access the document from Microsoft SharePoint or give access to others.”

In SQL, these Record Links are saved under the “Record Link” table, and contains (among others) the Record ID, URLs, Description, etc.

Basically, the Record ID should be able to uniquely identify to which record this link belongs, so…. Not surprisingly, this Record ID will contain the table number and corresponding key values (+ 2 null bytes).

In principle, what I have just written should be enough to understand the format, but I will further explain with the help of some examples.

Let’s start with Annette Hill, one of the employees that work at CRONUS International:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Annette’s Record ID on the Record Link table (in SQL) will look as “0x501400000089FF4148000000”. As I wrote before, this should correspond to the table number, the keys and 2 null bytes… That is:

The first 4 bytes correspond to the table number, in this case 0x50 14 00 00, which should correspond to table 5200.

Now, you must be wondering, how comes 5200 can be converted to such a strange binary representation. Well, we will have to remember two things: 1) The number needs to be in hexadecimal format, and 2) It needs to be encoded in little endian as we are working with Intel processors (just use your search engine, if you don’t know/remember).

So, we need 4 bytes for (table) 5200, that in hex is: 00 00 14 50, if we convert it to little endian, we will get: 50 14 00 00.

The next 2 bytes correspond to the data type of the key. The type is CODE, which is type 137, which is only converted to hex: 00 89. At this point you must be thinking that I am pulling your leg, as this is supposed to be further converted using little endian, but it turns out that the keys data types are encoded in their binary representation, in order to reuse code when searching for them.

The following 4 bytes correspond to the key itself. Interestingly enough, code data types will start with 1 byte that can tell you the length of the code (if numeric) or FF which would correspond to a character based code type. In this case, we have 0xFF, meaning that we have characters and those are 0x41 and 0x48 + null termination 0x00. Of course 0x41 corresponds to ‘A’ and 0x48 corresponds to ‘H’ (in “extended” ASCII), which is the key, as can be seen on the ‘No.’ field.

The last 2 bytes will always be null characters.

Let’s do something a little bit more interesting, now with Bill of Materials:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This link will look as: 0x5A0000000089FF313932342D57000087102700000000

The first 4 bytes correspond to the table number, in this case 0x5A 00 00 00, which should correspond to table 90.

The next 2 bytes correspond to the data type of the first key. The type is CODE, which is type 137, which is only converted to hex: 00 89.

The following 8 bytes correspond to the key itself. In this case, we have 0xFF, meaning that we have characters and those are ‘1924-9’ (+ null).

The next 2 bytes correspond to the data type of the next key. The type is Integer, which is 135, that is converted to hex: 00 87.

The following 4 bytes correspond to the key itself. In this case, we have 0x10270000, converting this to an integer, corresponds to 10000 (using the little endian rule).

The last 2 bytes will always be null characters.

If you are wondering where these values came from, Open Table 90, and verify that the Key is in fact ‘Parent Item No.,Line No.’ and that the values that we have decoded, correspond to the data we have added the links to.

Of course, you should always remember that this format is subject to change and there is no guarantee that it will be preserved on future versions, also remember the standard following claim.

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

SPN name Changes

It should be a common installation scenario to have NAV 2009 components installed on three different machines, one for the database hosted by SQL Server, one for the Microsoft Dynamics NAV Server (middle tier server) and the corresponding client machines. This installation is explained in MSDN “How To: Set Up Delegation”, however, what I wanted to talk about is the difference in SPN naming from 6.0 RTM and 6.0 SP1 (or hotfixes which are build 28795 or higher).

The names used before were in the format “NAVSERV_DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046”, the new name conventions are:

“DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046”, and

“DynamicsNAV/NAVSERV:7046”

 

Both names should be registered, to minimize possible issues.

This change should simplify the “Three tiers” installation in relatively complex name networks, for instance, when it contains multiple A or CNAME records.

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Installing Business Notification with SQL Server 2008

We are glad to announce that compatibility between Business Notification and SQL Server 2008 is as of now verified and confirmed. Business Notification can run on SQL Server 2008 provided that the 2 following components are also installed:

 

-          SQL Server Notification Services 2005

-          SQL Server 2005 Service Pack 3.

 

Notification Services is no longer part of SQL Server 2008. What SQL Server 2005 Service Pack 3 does is that it allows Notification Services 2005 to run with the SQL Server 2008 database engine. For further information, please also refer to "What's New in SQL Server 2005 SP3":

http://msdn.microsoft.com/en-us/library/dd353312(SQL.90).aspx

 

While refering to the Business Notification Installation Guide (https://mbs.microsoft.com/customersource/documentation/setupguides/installationmanualsnav2009.htm ) as your main source of information, below are the steps that you need to include in the installation process in order to use SQL Server 2008.

 

Installation steps:

 

1. Install SQL Server 2008.

2. Install SQL Server 2005 Notification Services only. When the SQL Server 2005 setup is started, a list of installation components is available. Selected Notification Services only and complete the installation.

3. Apply SQL Server 2005 Service Pack 3. This can be downloaded from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

 

Continue with the steps described in the Business Notification Installation Guide.

 

Smaranda Calin

Program Manager

 

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

An almost exact decimal

Recently I was working with a very interesting case, involving Automation, and AL runtime. This involves the number of decimals that NAV supports; giving the sensation that rounding is done incorrectly.

First, I would like to bring you your attention to Table 370 (Excel Buffer). This table provides a very simple way to read and write Excel files. This works pretty neatly, except when you start dealing with some “special numbers”.

The issue, really, is that decimals, when transported via COM, are encoded in a VT_R8, which means that we have 64 bits to express all decimal values (according to MSDN, VT_R8 is an IEEE 8-byte), which basically means, they cannot express extremely precise values.

Now, how exactly precisely is precise? Let’s work with 17.7.

Well, don’t think that NAV, or for that matter COM, cannot express this “simple” value, the problem is really they way decimals are encoded.

Let me first show you the issue, as it is pretty simple to reproduce.

First, create an Excel file using Microsoft Excel. On cell A1 of a Sheet, type “17.7”. Now, this by itself is tricky, because what you need to type depends on your Locale. What I mean is seventeen and 7/10 (meaning, that you might have to use comma instead of a dot).

Save the recently created file someplace that you can remember.

Now create a codeunit, with one global variable of type record of Subtype “Excel Buffer”.

Then open your file and refer to your Sheet, and read it, just like this:

 

It is time to see the inserted value, for that, go to Table 370. You will see that the Cell value is in fact “17.699999999999999” (if you see 17.7 is because your NAV version is not as accurate as the one I am using, or the codeunit has already been modified for rounding).

You must be thinking now that I am trying to sell you a platform bug as a feature, but this not the case. The problem, really, is that recent NAV builds recognizes more decimals (up to 18), and an 8 byte IEEE is not as precise. To illustrate this, look at the following table:

Decimal Floating-Point:	64 bit Hexadecimal (memory representation):
17.699999999999995	4031B33333333332
17.699999999999996	4031B33333333332
17.699999999999997	4031B33333333332
17.699999999999998	4031B33333333333
17.699999999999999	4031B33333333333
17.700000000000000	4031B33333333333
17.700000000000001	4031B33333333333
17.700000000000002	4031B33333333334
17.700000000000003	4031B33333333334
17.700000000000004	4031B33333333335

In here, it is clear that the hexadecimal representation “4031B33333333333”, not only refers to 17.7, but also to some “neighboring” values (actually 4 values), so, the COM algorithms seem to choose the most likely one: the middle, which in fact, is really a close approximation.

The reason why decimal accuracy was increased in NAV was precisely to improve calculations when dealing either with very large numbers, or very accurate ones, the consequence being that 64 bit representation cannot  deal with so many decimals.

Fortunately, in order to prevent this from happening, you can round the values to be less precise, which in most cases will give you the values that you were in fact expecting, so for decimals, you could round to 4 decimals using variants (in order to know if you are dealing with a decimal number or not):

    "Cell Value" := XlWrkSht.Range(xlColID + xlRowID).Value;

    IF ("Cell Value".ISDECIMAL) THEN

    BEGIN

      "Cell Decimal Value" := "Cell Value";

      "Cell Decimal Value" := ROUND("Cell Decimal Value", 0.0001);

      "Cell Value as Text" := FORMAT("Cell Decimal Value");

    END

    ELSE

      "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');

 

Hopefully this will clarify why you can get this approximations, and how to deal with it in C/AL.

Jorge Alberto Torres
Software Developer Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Cursor Types

One of the changes in Microsoft Dynamics NAV version 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920.

The change of cursor type can also mean a change in behaviour. With Dynamic cursors, SQL Server more often optimises for the ORDER BY - part of a SQL query than is the case with Fast Forward cursors. This is because a result set based on a dynamic cursor has to include new rows. IF SQL Server were to choose an index that fits the WHERE clause then it would have to sort all rows according to the ORDER BY before returning the first row to the client and that by definition is a STATIC result-set.

 

Take this query as an example:

SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"

WITH (READUNCOMMITTED)

undefined (("Document No_"=

ORDER BY "Item No_","Posting Date","Entry No_"

With Fast-Forward cursors, in this example SQL Server is likely to try to optimise for the WHERE clause, which is "Document No.". But with the ORDER BY clause specifying a different sorting, SQL Server may then chose a clustered index scan instead.

With Dynamic cursors, SQL Server is more likely to optimise for the ORDER BY clause, which is a valid and existing index. So in this exampe SQL server would chose an index scan on this index. You can see this by running the query from SQL Server Management Studio like this:

 

declare @p1 int set @p1=-1

--declare @p3 int set @p3=16+4096+8192 -- Fast Forward

declare @p3 int set @p3=2+4096+8192 -- Dynamic

declare @p4 int set @p4=1

declare @p5 int set @p5=49

--declare @p5 int set @p5=15 – FAST 15

exec sp_cursoropen @p1 output,N'

SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"

WITH (READUNCOMMITTED)

WHERE (("Document No_"=@P1))

ORDER BY "Item No_","Posting Date","Entry No_"

'

,@p3

output,@p4 output,@p5 output,N'@P1 varchar(20)',

'START'

select @p1, @p3, @p4, @p5

exec sp_cursorclose @p1

You can enable / disable the 2nd and 3rd line to switch between Fast-Forward or Dynamic cursor, and see the result in the query plan that gets generated.

 

How does this affect NAV?
The change in behaviour can mean that certain queries that ran without problems may be slow, after a customer upgrades the platform to the builds mentioned above. In cases

that we have seen, the problem has been limited to one or very few specific queries, typically on filtered forms, that were slow after upgrading the platform. Use the query from here:

Simple query to check the recent performance history II - now including Query Plan information

to help identifying which queries to trouleshoot. Note that the query shows the Cursor Type in the rightmost column. Then look at whether SQL Server has an index to match the ORDER BY clause.

Also, be extra careful using the SQLIndex-property on keys. By setting this property, the ORDER BY-clause may not match a SQL index anymore, and a Dynamic cursor will have

to scan the table.

 

Why this change?
Internal performance tests show that overall, Dynamic cursors give better performance and fewer blocks. So while the change may cause unforeseen changes in behaviour when a customer upgrades, overall we have seen better performance with Dynamic cursors.

 

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

The COPYSTREAM “problem” for Sockets

I must say that since I started working in the integration area of NAV (from within NAV), I got very interested in the possibilities that it would bring to NAV developers. Back then, I presented a Web Server for the NAS using the Socket ComCom and since then I have seen how people have used the streams and specifically the MSMQ Bus Adapter for integrating services and data.

When writing the code for the STREAMS (InStream and OutStream), I thought it would be very useful to write a function that could copy from one to another, reading from the InStream and writing to the OutStream. However, when using Sockets, there is an essential problem to completing this task. Since TCP data can contain anything (literally any data), and since the data size is also unknown and un-limited, how can we know when the copying has been completed?

Recently I was dealing with a problem that I found within one of the communities forums (yes, we are reading) and it was regarding XMLPorts. You need to stream the data to and from them, so I rapidly wrote a codeunit that would allow me to read the XMLPort to a file; for that, I used the COPYSTREAM function to read from the XMLPort to a FILE. A couple of minutes later, I found out that my file was not filled out correctly as it was truncated, and immediately went into the code to further debug the problem. I quickly found out that the problem was that the COPYSTREAM was not reading till the End of Stream (EOS). I was puzzled to find an error in the COPYSTREAM code after all this years, but of course I thought that the call to the EOS was simply missing. Once I saw that the call was in fact there, it finally hit me…. InStream.EOS is not working, or, it cannot really work.

The real problem here is, how do we know when a stream will not send us more data? Usually this is done through a protocol (like http, ftp, etc) but without a protocol, it is not possible to have a clear rule when we have the EOS. Is it a NULL termination? Is it 2 x CR+LF? Or what about a dot in an empty line? The truth is that, we could make rules, but we cannot have a generic answer to all possibilities.

Depending on your specific conditions, and specially the size of the data you are sending, you might not see this problem at all. But maybe, from time to time, you have noticed that data being received gets truncated and you are trying to figure out what went wrong.

To further illustrate the problem, let’s receive a file using Sockets and Save it on our local file (I’ll skip the sending code unit, but it can very easily be done following the samples provided in the Development Guide for Communication Components):

 

In the environment where I am writing this, I can send files without any issue up to approximately 25 Kb. After that, everything seems to work fine, except that the file gets truncated, that is, I am trying to send a 48 kb file, but get a truncated one.

In order to get rid of this problem, I suggest copying the stream one char at a time, so the code could look something like:

The retry is necessary, because we cannot rely on InS.EOS. InS.EOS also suffers from the fact that we don’t know when the stream will stop sending us data.

A nicer way to deal with this would be to first send how big the data is. That way, we don’t have to rely on time to assume that the information has indeed completed its transmission. If you are sending XML documents, you could also use the DOMDocument.load(InS) return value, to see if the received data indeed contains a valid XML document. If you have access to codeunit 7700, you can see an example on how this could be achieved (as you need to first copy the stream).

Of course, as mentioned before… This issue will only happen with the raw SocketBusAdapter.

Jorge Alberto Torres
Software Developer Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

The asymmetric stream

Since NAV’s Stream introduction, there has been confusion about when to use READ and when to use READTEXT; when to use WRITE and when WRITEXT.

As it is documented, InStream.READ  and OutStream.WRITE are meant to be in binary format, which means that Text strings will be ‘zero terminated’ when written, and a ‘zero terminated’ is expected when you are using READ; however READTEXT will not expect a ‘zero terminated’ text, but can accept one.

This very last feature makes the streams asymmetric, as you can write different binary formats than what you can read.

Take the following code snippet:

If we examine the file that gets created (in a binary text editor), you will notice that the Texts are in fact ‘zero terminated’, but the READTEXT will be able to read them individually.

Now, this is where the fun begins. We can change the READTEXT to be READ (as it should have been) and everything will continue to work exactly the same…, almost. You will notice that the readCount now (when using InS.READ) will include the zero termination, whereas before the count was only for the characters in the text. Actually, if you put a return value in the WRITE (char 13), you will notice that the zero termination is included in the count (because actually that was the binary format of the Text).

So, count in count := OutS.WRITE('My text.'); will be 9 (8 characters and a zero termination), whereas count := OutS.WRITETEXT('My text.'); will be 8. The reason why it was decided to return the written bytes, was precisely so that people would know how many bytes are sent through the Stream.

Another thing to take in consideration when working with Streams are CODE types. When writing CODE types using OutStream.Write, they are NOT written in NAV binary format. So they are not symmetric with the FILE.WRITE function; however, InStream.READ(CodeType) will accept either data written using OutStream.WRITE(CodeType), or FILE.WRITE(CodeType).

Another possibility is to write data using OutStream.WRITETEXT(Text) and read it via InStream.READ(Text). Just take in consideration that when using OutStream.WRITE(Text), the Text will be zero terminated and when using OutStream.WRITETEXT(Text), it will not. 

Jorge Alberto Torres
Software Developer Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Simple query to check the recent performance history II - now including Query Plan information

One of the queries I use the most, is the pplan-cache query from this post:

Simple query to check the recent performance history

The good thing about the query is that it shows information that could otherwise require a lot of work (collecting and analysing traces files). And the query does not need any advance work or setup. It can just be run. So it's a very easy way to receive information from a system, which is often very useful as a first step in troubleshooting performance. For more details about the result of the query, refer to the post linked above.

 

Below is a slightly enhanced version of the query. Since the query is based on the cache of compiled query plans, it is not a big step to extend it to also include the query plan itself, and even extract certain information from the plan if you know what you are looking for.

So this query does the same as the original one, but with the following additions:

• New column query_plan is included. It shows the query plan as xml which may be difficult to read, but it contains the full plan. Note: Some times, for no apparent reason, the query plan can't be retrieved, so it may not show the query plan on all lines.
• cursor_type, just as an example of how to retrieve data from the query plan. If you find other things in the plans that may be interesting, then use the syntax to retrieve this further information.

 

Here is the updated query

 

SELECT TOP 100

SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) as statement_text,

execution_count,

case

when execution_count = 0 then null

else total_logical_reads/execution_count

end as avg_logical_reads,

last_logical_reads,

min_logical_reads,

max_logical_reads,

plan_handle,

ph.query_plan,

-- Query Plan Information

case when

ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]') = 0

then '' else

ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]','nvarchar (max)')

end as cursor_type

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as ph

ORDER BY max_logical_reads DESC

:

 

What I would really like, is to receive feedback on what parts of the query plans are useful. Then extend the query even further to include as much useful information as possible. For exdample, in some cases the query plan contains missing index-information. The lines below can be copied into the query above to include this information. Any feedback on whether this is useful or not, and whether other information from the query plans can be useful is really very welcome. You can add comments about this below.

 

-- Missing Indexes

,case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup)[1]') = 0

then ''

else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/@Impact)[1]','nvarchar (max)')

end as missing_index_impact,

 

case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]') = 0

then ''

else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]','nvarchar(max)')

end as missing_index_table,

case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]') = 0

then ''

else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]','nvarchar(max)')

end as missing_index_field

 

 

 

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Session Monitor for SQL Server 2005

Session Monitor is not a new tool, but it did stop working with SQL Server 2005. So I think that a lot of people stopped using it, which is a shame. The things that were broken now work again, and I would recommend anyone with any kind of blocking problems to implement this tool. With this post I hope to get some people to use it again, and to provide a new tool to anyone who did not know it exists.

Purpose of the tool

The purpose of the Session Monitor tool is to show a live picture of any current blocks in the Microsoft Dynamics NAV database. And to show it in a familiar environment, i.e. a NAV form. It is an extension of the information you get when going to File -> Database -> Information, and drill down on Sessions.

What the tool does:

• Shows live information about who is blocked by whom
• Shows which user is at the top of a blocking chain
• Shows if a user is involved in blocking, i.e. either being blocked or is blocking someone. So you can filter out all sessions that are not involved in blocking
• Shows the wait resource, i.e. on which table the block is

This is how the Session Monitor shows a blocking situation:

As default it refreshes every second.

Some of the useful features of Session Monitor are:

• Filter on "Involved in Blocking". Then, as soon as there is a block it will show up. This is especially useful when you have more sessions that can fit on the screen, which means that there might otherwise be a block outside of the form.
• Wait Resource tells you on which table the block is, which helps you get some idea about in which area of the application it is.
• Originating blocker ID shows the head-blocker. This is useful when a user is blocked by someone who is blocked by someone else, etc.
• Wait Time shows how long the block has been there.

How to get it to work

As mentioned, the tool stopped working in SQL Server 2005. But there are corrections for this now. First, download the tool, which is part of the "Performance Troubleshooting Guide", available for download here (PartnerSource login required):

Microsoft Navision SQL Resource Kit

The original tool contains a SQL query (Session Monitor (SQL Server).sql) which is the part that doesn't work for SQL Server 2005. So don't run this query.  Run the query from this KB article instead (PartnerSource login required here too):

KB 933042 - Error message when you use the Session Monitor feature in Microsoft Dynamics NAV: "Invalid length parameter passed to the substring function"

Note!: Run the query on the NAV database - not on Master, or any other database you have on SQL Server.

In NAV, import the object "Session Monitor (SQL Server).fob" from the Performance Troubleshooting guide.

Make one change - otherwise in some cases it will fail with an INSERT error:

Design codeunit 150011 "Session Monitor Mgt. (SQL Srv)", and replace this line:

RefreshUserActivity()
SELECTLATESTVERSION;
IF Session.FIND('-') THEN
  REPEAT
    SessionTmp := Session;
    // SessionTmp.INSERT; Remove this line and replace with:

    IF SessionTmp.INSERT THEN ; // New line  
  UNTIL Session.NEXT = 0;

Those are the changes needed to get it to run on SQL Server 2005,

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

How to deploy multiple NAS’es in ADCS

Although this is not related to the ‘core C/Side’, I would discuss about configuring ADCS with multiple NASes.

The first question would by why somebody would like to do this? The answer is very simple. On a single VT100 NAS installation, whenever a handheld user is performing a time-consuming task, any other handheld user will have to wait for the task to complete, before getting a response in the screen. The reason is because the NAS can perform one task at a time, and while it is busy, it will queue any other incoming requests. Another good reason is because the VT100 will round-robin the available NASes making the up-time more reliable.

The installation is simple, as long as you configure the system like this (TCP ports are examples):

For security reasons, it is recommended that the Access Point uses encryption (to the handhelds), and that this sub-network is private between the handhelds and the VT100.

The NASes can be on the same machine or on different boxes, as long as you remember to set up the registry correctly where the VT100 service is installed. If the NASes are on different boxes, remember to generate an encryption key per box, and to copy the public key to the corresponding entry in the registry of the VT100 box. Of course the installation gets simplified if the NASes run on the same box, and even more if the VT100 and the NASes share the same hardware.

Let’s make a step by step installation (assuming a NAV 5.00 or 5.00 SP1).

1.       Install the Software (Database + NAS + VT100 + C/Side) as described in the manual.

2.       Run C/Side using Windows Authentication. Remember to use the same credentials that the NAS will get (else the encryption key cannot be read by the NAS).

3.       Generate the encryption key (one per machine – not per NAS).

4.       Set each NAS to connect to the corresponding (same for all NASes) database, company, etc, using the same Windows login credentials as you logged in to generate the encryption keys.

5.       Because of a VT100 limitation, each NAS will have to get a different TCP port number, even if they are located on different machines, so, the startupparameter should be “ADCS ADCSID=#”, where # refers to a different TCP port.

6.       Now, the really tricky part comes from here. For each NAS that the VT100 will connect to (that is, on the machine where the VT100 service will run) you will need an entry in the registry, in order to connect the VT100 with the corresponding NAS. Each entry should be under “HKEY_LOCAL_MACHINE\SOFTWARE\Navision\ADCS\Application Servers”. The ADCS installation will create “ADCSNAS1”, and you can continue creating an entry per NAS on the same level (let’s say “ADCSNAS2, ADCSNAS3”, etc).

7.       If the NAS is on a different machine than the VT100, you also need to set the ‘SocketServerName’ and portnumber, where the first one refers to the machine name (or IP address) of the host of the corresponding NAS, and the second one, the portnumber (i.e. 11322). Both of these keys are ‘String Value’.

8.       For each NAS, you will need the ‘CryptBlockSize’, ‘Key Size’ and ‘Public Key’ (the ‘Private Key’ should not be copied), and this ‘Public Key’ should be copied from the machine where the NAS is hosted.

a.       NOTE- The machine that hosts the VT100 will need one ‘ADCSNAS’ entry per NAS that it connects to, but the machine(s) that hosts several NASes will only need one ‘ADCSNAS’ that will share the same keys (generated by the C/Side client), but different ADCSID startupparameter.

9.       Now you should start the NASes and the VT100 services on the corresponding machines. To test that everything works as it should, you should be able to stop any of the NASes on your system, and the screens should not halt (however, the response time might be slower). Also, when not all NASes are available, new sessions might not be successful, but any existing ones should be fine.

With this configuration you benefit on the simultaneous response time for the handhelds, as different NASes will be able to process in parallel even when the NASes are hosted on the same machine. Also, as discussed before, this helps minimize down times, as even when a NAS stops or gets restarted, the VT100 will retry from the ‘pool of NASes’ when a TCP timeout occurs. Because of this, don’t set NASes on the registry that will not be deployed, as this will in fact slow down the system.

---

 

Jorge Alberto Torres
Software Developer Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

The random events

 Some years ago I was dealing with an issue that was driving me crazy and it took many hours to figure out what was wrong with the code. It was until I was explaining my findings to a colleague (don't you solve lots of things this way?) that it hit me, and when looking at the original (NAS) code I confirmed what was wrong.

What was more irritating was that the system was working most of the time, and, of course, there was no way to consistently reproduce the issue.

I will explain the issue here, hoping that people don't make the same mistake again.

The problem

Think about a single instance code unit which is receiving an event in order to process the incoming data (this was using the ComCom objects), and, in order to catch possible errors in the AL execution, a NAV Timer (Navision Timer) is used to reply successfully or with an error to the receiving event initiator (the COM component that started the conversation).

Normal flow (from the NAS perspective) was:

• ComCom2::MessageReceived would be invoked.
• GlobalVal, a global variable in the single instance Codeunit would be filled with the incoming data to be processed.
• NTimer would be enabled, in order to fire relatively soon (I think it was something like 20 milliseconds).
• NTimer::Timer would fire and would get the value from GlobalVal to process the data and reply to the connection (by NAV architecture, this connection had to be a new one, as, when the MessageReceived event finishes, the ComCom connection drops).
• If any AL error would arise within the NTimer event, the NTimer::TimerError would be triggered, which would allow us to reply an error to the calling component (using the GlobalVal to identify which connection would have to be informed about the problem).

Sounds reasonable, and in most cases this would work great. However, people were experiencing mixed up answers especially when errors were to be reported.

Of course, my first thought was that the component connecting to the NAS was the guilty one, as, after all, it is a multi-thread, multi-connection component (it runs as a Windows Service), and getting all this threads straight is always a tricky business. However, after inspecting the components code, and correcting some issues, the problem was still there.

I decided it was time to get the hands really dirty and started making my own component which would simulate the system and start hammering the NAS with requests using multiple threads and inspecting the replies, and sure enough, especially when the NAS would error, the replies would contain a different ID than the original sending ID on the same virtual connection.

So, there must be something in the NAS that does not work correctly. More code inspection, but nothing showed up until.... Hey, wait a second... We are using a global variable to save data and then reusing this data later on when the NTimer event fires, and, since the NAS is single threaded, this should all work fine, right?

Well, of course the first thing that came into my mind was that there was a possibility that a new incoming event would be "queued" before the NTimer event would be scheduled, and this would mean that a new MessageReceived event would be triggered before the NTimer could even process the previous message. Bingo! That is the issue!

After further investigating the NAS code, it turns out that, although the previous scenario is possible, it actually does not queue sequentially. Incoming events are basically random when the host COM cannot handle the call. I could go very deeply here, but what happens is that, when the NAS COM event is called, the NAS actually checks to see if it is already handling another incoming event. If this is the case, it signals that it cannot process the incoming event (via a RPC_E_SERVERCALL_RETRYLATER) and COM basically waits "some time" before it retries the call. The problem is that this "some time" is not known and it changes, and that is when the trouble really starts.

Basically, what happened in our scenario was that 2 consecutive MessageReceived events will fire (before the first NTimer would) and obviously one GlobalVal would be lost and we would reply twice  to a single event (the NTimers would fire, but with the same GlobalVal).

How to solve this

The cure was simple. When the MessageReceived event is fired, we process the information and reply to the event on the same connection. After all, ComCom objects do report back AL errors in XML format, so, in principle, there is no need to complicate matters.

So, after getting rid of the NTimer, and replying to the incoming request within the MessageReceived event, everything works as expected.

---

Jorge Alberto Torres
Software Developer Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Jobs Update for Microsoft Dynamics NAV 5.0 SP1 is now released.

I am pleased to announce the release of Jobs Update for Microsoft Dynamics NAV 5.0 SP1. This update is now available for you to download from Partner Source. Login credentials are required. https://mbs.microsoft.com/partnersource/downloads/releases/MicrosoftDynamicsNAV50SP1.htm

Jobs update rollup corrects the issues that are described in KB article number 954191.

 

These postings are provided "AS IS" with no warranties and confer no rights.
You assume all risk for your use.

 

Mtoo Norrild
Program Manager