To make things just a little bit simpler, some of the NAV blogs have now been combined into the NAV Team Blog. So from now on, that is where we will blog. All content from this blog has already been migrated to the NAV Team Blog.

So this post will be the last post on this blog. And please check for updates on http://blogs.msdn.com/nav and continue to add as valuable comments and feedback there, as you have done here.

More information about what is happening with the NAV blogs here:

New Colors, New Design, and Tons of New Content 

Thanks for all support, comments, feedback and time, and see you on http://blogs.msdn.com/nav

Lars

The usual rule for specifying a key in NAV is, that it will chose the first key that matches all the fields specified by SETCURRENTKEY, RunformLink, etc.

Example:
Table 21 "Cust. Ledger Entry" has the following keys (not all of them listed):
 
  - Entry No.
  - Customer No.,Posting Date,Currency Code
  - Customer No.,Open,Positive,Due Date,Currency Code
  - Customer No.,Applies-to ID,Open,Positive,Due Date
 
You have code like this:
CustLedgEntry.SETCURRENTKEY("Customer No.");
CustLedgEntry.FINDFIRST;
  
You will get a query with this ORDER BY:
ORDER BY "Customer No_","Posting Date","Currency Code","Entry No_"
 
So far, all works as expected: NAV finds the first key that matches the sorting, and uses that.
 
 
This changes if you change the SQLIndex property on the key. If we design table 21 and set SQLIndex = "Customer No." on the key used above, then NAV will skip this key and use the next matching one, and the code above will now generate this ORDER BY:
ORDER BY "Customer No_","Open","Positive","Due Date","Currency Code","Entry No_"
 
So now, even if we want to sort by "Customer No.", and we have a SQL Index which is exactly that, NAV chooses a different index. And it no longer follows the rule of choosing the first key available which satisfies the requested sorting.
 
So specifying SQLIndex on a key makes this key less likely to be chosen by NAV. Of course, this does not affect which index SQL Server actually decides on when it makes its query plan. It only afects the ORDER BY clause.
 

The reason for this is:

Above is mentioned that we want to sort on "Customer No.". This is not the whole truth. NAV always adds the primay key, so actually we want to sort on "Customer No.", "Entry No.". The primary key fields are used for specifying a sorting (ORDER BY) that is deterministic and they are also used to construct SQL for calls to Record.NEXT(), which might be needed to reposition itself in the database. This happens a lot from the UI but also from C/AL code.

So the short story is: When SQLIndex is used, they key will by definition not match the ORDER BY which is based on the NAV key, and the index is likely to not be unique, so NAV will give the key a lower priority when there are other similar keys which do not have SQLIndex specified.

Only when a SQL index is NOT specified, NAV automatically adds the primary key field(s) when it creates the index on SQL server. When we do add SQLIndex, this does not happen - the SQL Index will be exactly what you specify in this property.
 
In this example where we set SQLIndex to "Customer No.", the SQL Index will just be just that - "Customer No_", so it will not be unique and it will not satisfy an ORDER BY on “Customer No.,Posting Date,Currency Code, Entry No.” and it will not satisfy calls to Record.Next(). So when SQLIndex is specified, NAV will continue down the list of keys, looking for the next key that matches the chosen sorting (and does not have SQLIndex specified) and use that instead.
 
 
It’s recommended to use the SQLIndex property with great caution. Also see previous blog posts:
Beware the SQL Index property on NAV 5.0 SP1 
SQLIndex property
 
If you do use it, then also be aware of the changes in behaviour described here
 
 

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

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

After Microsoft Dynamics NAV 2009 SP1 release more and more developers start using it and trying to adopt existing solutions for new 3tier environment.
Most workload comes from trying to adopt current forms to new object - pages.
Particularly transformation could be done by using TransformationTools (http://msdn.microsoft.com/en-us/library/dd338789.aspx), however it is not "best ever" and partners reporting problems and require to fix it...

But it isn't so easy...

This tool is delivered to us from dev team and it helps us to make transformation faster, but it is supplementary tool - we can use NAV without this tool and we can "convert " our solutions without it - do it manually.
So "big thanks" to dev team for this tool, however we can't expect that dev team will fix all problems in tool with the same priority as base products (Microsoft Dynamics NAV).

And we can't expect fixes for problems related to incorrect transformed statements - after transformation pages can't be compiled...
Problem is in tool simplicity: it searches for text and convert it to another text. Converting rules are described in file CodeRules.txt (http://msdn.microsoft.com/en-us/library/dd338843.aspx).
But simplicity is as strength as weakness of this tool - only text described in CodeRules.txt file will be converted, if there are any differences in text - transformation will be incorrect.
For example:
In form code we have statement:
CurrForm.Number.UPDATEFONTBOLD(Number);
Then after transformation on page will be created new variable NumberEmphasize
And statement will be converted to:
NumberEmphasize := Number;
It is because UPDATEFONTBOLD is not used in pages and must be removed.

So far so good.
Tranformation will be done correct for statements:
CurrForm.Number.UPDATEFONTBOLD(Number1=Number);
CurrForm.Number.UPDATEFONTBOLD(Number1>Number);
and etc. because transformation rules are described in CodeRules.txt

But transformation tool is looking for direct text fit to rules and will not transform text which is not described in Coderules.txt. 
If code becomes little more complicated (not described the same syntax as CodeRules) - transformation tool capitulates.
For example next code will not be transformed:
CurrForm.Number.UPDATEFONTBOLD(Number1<Number);
CurrForm.Number.UPDATEFONTBOLD(Number1=xRec.Number);
CurrForm.Number.UPDATEFONTBOLD(xRec.Number1=Number);
CurrForm.Number.UPDATEFONTBOLD(Number1=Number2=Number3);
CurrForm.Number.UPDATEFONTBOLD(Number1=(Number+100));
...

Yes... We can make rules for these statements too (dev team delivered rules file), but we will never describe everything what could be written by happy-creative developpers around the world...
Maybe some rules could be never used? Who knows..? 

I can collect all requirements for CodeRules.txt and periodically release new one (fixed). Do you want to order me do it, let me know in comment to current post :) ...

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

Gedas Busniauskas

Microsoft Lithuania
Microsoft Customer Service and Support (CSS) EMEA

The query below combines these three queries into one:
Index Usage Query
Recent Bocking History
Table Information Query

It can be used to just see the number of records in each table. But also by just changing "ORDER BY", it can be used to see which index cause most blocking / wait time / updates or locks. Or to compare Index Updates with Index Reads to get an idea of cost versus benefit for each index for the purpose of index tuning.

So in short, one query gives you:
  - Index / Table Information
  - Index usage (benefits and costs information for each index)
  - Index locks, blocks, wait time and updates per read (cost/benefit).

The query must be run in your NAV database. It will create a table called z_IUQ2_Temp_Index_Keys and use various Dynamic Management Views to collect information for each index into this table. First time you run it, or if you want to refresh data, you must run the whole query which may take up to a minute of two for each company in the database. After that if you just want to change sorting / get the results again, then you only need to run the last part of the query, beginning with:

The last lines suggest various "ORDER BY"s that might be useful to enable instead of the default one, which is by Table Name.

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

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

IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL

DROP TABLE z_IUQ2_Temp_Index_Keys;

create table z_IUQ2_Temp_Index_Keys(

[l1] [int] NOT NULL,

[F_Obj_ID] [int] NOT NULL,

[F_Schema_Name] [nvarchar] (128) NULL,

[F_Table_Name] [nvarchar] (128) NOT NULL,

[F_Row_Count] [int] NULL,

[F_Reserved] [int] NULL,

[F_Data] [int] NULL,

[F_Index_Size] [int] NULL,

[F_UnUsed] [int] NULL,

[F_Index_Name] [nvarchar] (128) NOT NULL,

[F_Index_ID] [int] NOT NULL,

[F_Column_Name] [nvarchar] (128) NOT NULL,

[F_User_Updates] [int] NULL,

[F_User_Reads] [int] NULL,

[F_Locks] [int] NULL,

[F_Blocks] [int] NULL,

[F_Block_Wait_Time] [int] NULL,

[F_Last_Used] [datetime] NULL,

[F_Index_Type] [nvarchar] (128) NOT NULL,

[F_Index_Column_ID] [int] NOT NULL,

[F_Last_Seek] [datetime] NULL,

[F_Last_Scan] [datetime] NULL,

[F_Last_Lookup] [datetime] NULL,

[Index_Key_List] [nvarchar] (MAX) NULL

CREATE NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

([F_Obj_ID] ASC

CREATE NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

([F_Index_ID] ASC

CREATE NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

([F_Row_Count] ASC

insert into

(row_number() over(order by a3.name, a2.name))%2 as l1,

a1.object_id,

a3.name AS [schemaname],

a2.name AS [tablename],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

a1.data * 8 AS data,

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,

SI.name,

SI.Index_ID,

index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),

US.user_updates,

US.user_seeks + US.user_scans + US.user_lookups User_Reads,

IStats.row_lock_count + IStats.page_lock_count ,

IStats.row_lock_wait_count + IStats.page_lock_wait_count,

IStats.row_lock_wait_in_ms + IStats.page_lock_wait_in_ms,

when (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) and (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) then US.last_user_seek

when (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) and (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) then US.last_user_scan

else US.last_user_lookup

end as Last_Used_For_Reads,

SI.type_desc,

SIC.index_column_id,

US.last_user_seek,

US.last_user_scan,

US.last_user_lookup,

(SELECT

ps.object_id,

SUM (

WHEN (ps.index_id < 2) THEN row_count

ELSE 0

) AS [rows],

SUM (ps.reserved_page_count) AS reserved,

SUM (

WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

) AS data,

SUM (ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN

(SELECT

it.parent_id,

SUM(ps.reserved_page_count) AS reserved,

SUM(ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

WHERE it.internal_type IN (202,204)

GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

inner join sys.indexes SI ON (SI.object_id = a1."object_id")

inner join sys.index_columns SIC ON (SIC.object_id = SI.object_id and SIC.index_id = SI.index_id)

left outer join sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id and US.index_id = SI.index_id and US.database_id = db_id())

left outer join sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_id())

WHERE a2.type <> N'S' and a2.type <> N'IT'

order by row_count desc

declare IndexCursor cursor for

select F_Obj_ID, F_Index_ID from z_IUQ2_Temp_Index_Keys

for update of Index_Key_List

declare @objID int

declare @IndID int

declare @KeyString VARCHAR(MAX)

set @KeyString = NULL

open IndexCursor

set nocount on

fetch next from IndexCursor into @ObjID, @IndID

while @@fetch_status = 0 begin

set @KeyString = ''

select @KeyString = COALESCE(@KeyString,'') + F_Column_Name + ', '

from z_IUQ2_Temp_Index_Keys

where F_Obj_ID = @ObjID and F_Index_ID = @IndID

ORDER BY F_Index_ID, F_Index_Column_ID

set @KeyString = LEFT(@KeyString,LEN(@KeyString) -2)

update z_IUQ2_Temp_Index_Keys

set Index_Key_List = @KeyString

where current of IndexCursor

fetch next from IndexCursor into @ObjID, @IndID

end;

close IndexCursor

deallocate IndexCursor

delete from z_IUQ2_Temp_Index_Keys

where [F_Index_Column_ID] > 1

[F_Table_Name] TableName,

[F_Row_Count] No_Of_Records,

[F_Data] Data_Size,

[F_Index_Size] Index_Size,

[F_Index_Name] Index_Name,

[F_User_Updates] Index_Updates,

[F_User_Reads] Index_Reads,

when F_User_Reads = 0 then F_User_Updates

else F_User_Updates / F_User_Reads

end as Updates_Per_Read,

[F_Locks] Locks,

[F_Blocks] Blocks,

[F_Block_Wait_Time] Block_Wait_Time,

[F_Last_Used] Index_Last_Used,

[F_Index_Type] Index_Type,

from z_IUQ2_Temp_Index_Keys

order by F_Table_Name

The NAV 2009 documentation walkthroughs provide step-by-step instructions for installing NAV 2009 on 2 or 3 machines. However, we have found that some of the same configuration issues come up time after time after installation.

When on calls with partners and customers, it seemed to me that this information was spread out all over the place, so I wanted to organize it in a different way for troubleshooting purposes so that I would have most everything in one place. Hopefully this will be helpful to others as well.

The intention of this post is to provide a checklist of sorts for troubleshooting some of the areas where we frequently find errors or omissions in configuration after NAV 2009 has been installed. 

Errors on the RTC resulting from configuration problems include but are not limited to...

Login failed for user...

A Server was not found at...

The User ID and password are invalid

*************************************

Before anything else, check to see what accounts are running the NAV and SQL services. Make note of domain account names, machine names, and FQDN (Fully qualified domain name)

Check for Incorrect SPN’s

When the NAV Server and the database are on separate machines AND the NAV Service is running under a Domain Acct:

2 SPN’s have to be set up for the NAV Service. SERVER will be different depending on your server name.

i.e. Server_DynamicsNAV/Server.FQDN:7046

Examples…

SERVER_DynamicsNAV/SERVER.NAV2009DC.LAB:7046

SERVER_DynamicsNAV/SERVER:7046

SPN for SQL Service is not needed IF SQL is running under NetworkService.

SPN for SQL IS needed if SQL is running under a Domain account – see "SQL SPN" at the end of this post

When the NAV Server and the database are on separate machines, AND the NAV Service is running under NetworkService account:

No SPN is needed for the NAV Service.

SPN for SQL Service is not needed IF SQL is running under NetworkService.

Tools

Tools are normally installed on Win 2008 by default (depending on features selected). For 2003, install the Windows Support Tools

-          Windows 2003 Support Tools

o   ADSIEdit.msc

o   SETSPN.exe

Setting the SPN :

o   Run the ADSI Edit tool on any server computer in the domain. To do this, click Start, click Run, type Adsiedit.msc, and then click OK.

In the ADSI Edit window, expand Domain, expand DC, expand CN=Users, right-click CN= AccountName, and then click Properties.

Note: The AccountName placeholder represents the domain account you are using to start the NAV Server (and/or SQL) service.

In the Properties dialog box, double-click the servicePrincipalName attribute to open the Multi-valued String Editor dialog box. (There are a few shortcuts to find the servicePrincipalName, you can check the ‘Show only attributes that have values’ to shorten the list or click in the Attributes box and type ‘ser’ to jump close to the attribute.)

In the Value to add box, add a SPN for the NAV Server (or SQL Server), and then click Add, keeping in mind that the SERVER will be different depending on your server name.  For SQL SPN see Appendix A.

SERVER_DynamicsNAV/FQDN:7046

Note: Replace “SERVER” with the name of your server, and “FQDN” with the fully qualified domain name, such as “SERVER.MICROSOFT.COM”.

In the Value to add box, add a SPN for the NAV Server, and then click Add. Keeping in mind that the SERVER will be different depending on your server name.

SERVER_DynamicsNAV/SERVER:7046

Note: Here for the “SERVER” value, only specify the name of the server.

Click OK two times.
Close the ADSI Edit window.

Check Delegation

When running the NAV Service under a Domain Account:

Delegation has to be set up for the account running the NAV service.

Note: The Delegation tab will only be present after adding the SPN to the domain user account.

o   Click Start, then click Run.

o   Type in dsa.msc and click OK.

o   Expand the Domain and then click on Users.

o   Locate the domain user account you are using , right click and select Properties.

o   Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK. (This is not constrained delegation as mentioned in the Walkthrough, but this makes it a little easier to setup delegation. You can always come back after it is setup and working to implement constrained delegation.)

o   Close the Active Directory Users and Computers window.

o   Note: for Constrained delegation, select Trust this user for delegation to specified services only and then select MSSQLSvc.

When running the NAV Service under NetworkService Account:

Delegation has to be set up for the machine running the NAV service.

o   Click Start, and then click Run.

o   Type in dsa.msc and click OK.

o   Expand the Domain and then click on Computers.

o   Locate the computer name, right click and select Properties.

o   Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK.

o   Close the Active Directory Users and Computers window

Check SQL Logins and OCL

Adding the login(s) to SQL and setting up the Object Change Listener (OCL):

OCL is NOT required if the NAV Server and SQL Server are on the same machine AND the NAV service is running under Network Service.

If using a Domain User to run services, make sure the login has been added to both SQL and NAV. Also check that user has Full Control to the server folder.

o   The account may already exist in SQL but the permissions must be manually set correctly):

Open Microsoft SQL Server Management Studio.
Click Security to expand the tree-view, right-click Logins, and then select New Login.
This opens the Login - New dialog box.
Add the domain user account in the Login name field, using the following format:

domain\domainUser

Click OK to exit the Login - New dialog box.
Click Databases, Demo Database NAV (6-0) or other database name, and then click Security to expand the tree view.
Under Security, right-click Users, and then select New User.
This opens the Database User - New dialog box.
Add the domain user account in the User name and Login name field, using the following format:

domain\domainUser

o   Add $ndo$navlistener in the Default schema field.
Click the Securables page.
Click Add, click OK, click Object Types, check Tables and then click OK. Click Browse, check the [dbo].[Object Tracking], click OK, click OK again.
In the Explicit permissions check Grant on the Select permission.
Click OK to exit the Database User - New dialog box.
Close Microsoft SQL Server Management Studio.

If the NAV server and SQL Server are on different machines AND the Network Service Account is running the NAV Service, then the Login and OCL must be set up using the same steps but for the machine account rather than the domain user…
Use the above steps, but replace the domain account with the machine account, i.e. <domain>\<computername>$

Check the configuration of Delegation for the RTC

Change the ClientUserSettings.config on the computer running the RTC, under the current user's profile, to define that a domain user account is to be used when connecting to the NAV Service tier.

On Windows Vista or Windows Server 2008, the default location is:

X:\Users\\AppData\Local\Microsoft\Microsoft Dynamics NAV

On Windows XP or Windows Server 2003, the default location is:

X:\Documents and Settings\\Local Settings\Application Data\Microsoft\Microsoft Dynamics NAV

Add the following key to the file:
<add key="DelegationInfo" value="DomainUser"></add>

There are two possible values: NetworkService and DomainUser. To enable delegation, set the parameter to DomainUser.

This will need to be repeated for all workstations that will be using the RTC.

After confirming all items above and making any changes, be sure to stop and start the NAV Server service before you attempt to re-connect using the RTC. If you still encounter the error message, remember that Kerberos tickets last for 10 hours, so if you add/change the SPN, you may either have to wait for any existing tickets to expire or download KerbTray and attempt to expire any existing tickets. This is found in the Windows Server 2003 Resource Kit, which can be downloaded from:

http://www.microsoft.com/Downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en

Check the SQL SPN (if applicable)

SPN must be added for SQL Server when running under a domain account.

There are errors in the documentation walkthrough regarding how to create the SPN for SQL. The easiest way to check for the correct SPN syntax is to look at how it automatically generates the SPN when running with network service (switch the account running the SQL service to NetworkService, check the SPN and use that to set up the SPN for a Domain Acct.

An example might be...

MSSQLSvc/LALA1719334.NAV2009DC.LAB:1433

where

SQL Service = MSSQLSvc

Server = LALA1719334

Domain = NAV2009DC.LAB

Port = 1433

Laura K. Lake

Microsoft Dynamics NA

Microsoft Customer Service and Support (CSS) North America

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

A new translator service is now available for all whose browser is set to one of the following languages:

English, German, French, Italian, Spanish, Portuguese, Netherlands Dutch, Russian, Chinese Simplified, Chinese Traditional, Japanese, Korean, Arabic.

To use it: Scroll down, and on the right hand side of this page, select your language under "Translate this site using Windows Live Translator".

Lars Lohndorf-Larsen

Microsoft Dynamics UK
Microsoft Customer Service and Support (CSS) EMEA

Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block wait time, etc.:

The tool is using these three SQL Queries / DMVs:

• sys.indexes: Basic information about indexes
• sys.dm_db_index_usage_stats: No. of Index Reads, updates, etc
• sys.dm_db_index_operational_stats: Index blocking / wait time etc.

The information that is collected is already described in these posts:

• Simple query to check the recent blocking history
• Index Usage Query

So the new thing is that now the information is read into NAV, making it more easy to browse through it, get an overview, and having information from various queries collected in one place.

Note:

• The tool, relying on Dynamic Management Views (DMVs) will only work for SQL2005 and later (not SQL2000).
• The information that is collected is since last time SQL Server was restarted.

The tool contains the following objects:

And it assumes that you have a partner's developers license.

To run it:

Run form 74102 "IUIT Table List". First set up the SQL connection by clicking Table -> Setup, and specify SQL Server Name, Database Name and credentials.

Then go back to the "IUIT Table List"-form, and click Functions -> Update.

After the tool has collected the information (takes a few minutes), you can sort your tables by a number of factors which can all affect performance. And you can look at indexes for each table (Table -> Indexes (Ctrl+F5)), and get statistics for each individual index.

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

Lars Lohndorf-Larsen

Microsoft Dynamics UK
Microsoft Customer Service and Support (CSS) EMEA

A few weeks ago I saw an up tick in the number of cases having problems accessing exposed web service. Some of the problem has been because the “CRONUS USA, Inc.” company name converts to an unfamiliar “CROUNS_USA_x002C__Inc”. This will be changing in NAV 2009 SP1, but until that time it would be helpful to know exactly how company names are converted for NAV Web Services. The rules are:

The character '%' are converted to the word “Percent”.

The characters '@', '"', '.', '/', '(', ')', '\', '-', ' ' (blank space) are converted to “_” with the following two additional rules.

- no two succeeding “_”’s
- no trailing “_” 

This covers most cases and only applies when replacing the above characters, not with the following "special characters".

Any remaining “special characters” are converted to “a valid XML name” (basically a numeric value, for example, the ampersand ‘&’  are converted to “_x0026_”). When a "special character" is converted like this, you could have two succeeding underscores ('_').

List of known special characters (please update as needed.):

• The ampersand ‘&’  are converted to “_x0026_”
• The comma ',' is converted to “_x002C_” (i.e. 'CRONUS USA, Inc.' would be converted to 'CRONUS_USA_x002C__Inc')

Additional, the How-To “Use SystemService to Find Companies” in the NAV 2009 Developer and IT Pro Documentation explain how to call the Companies operation on the SystemService to list the available companies with the converted names.

Scott Wright

Microsoft Dynamics North America

Microsoft Customer Service and Support (CSS) North America

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

The default methods exposed when a page is published are the so-called CRUD (Create, Read, Multiple and Delete) methods. All of those does not allow to set up previously a key for sorting order nor if the records have to be ascending or descending.

In order to retrieve data in a particular sorting order it should be necessary to develop N pages for N related sorting keys. This can be a solution that deserve the analysis of the cost of the pages plus the maintenance of the source objects.

Another way to set the Key and Sort order for pages object is described here. It only has the cost of purchasing one table that collect the User ID, the page called (Page ID) , the Key used and the Sort Order, plus one page to publish the table and only one page per master table. This will avoid the cost of N pages for master table per N Sorting order keys.

The following example is based on Customer table and has the purpose of develop a WinForm that dynamically retrieves data from the server based on a selected sorting order like in the picture below (just 3 keys have been used here):

PREREQUISITES

1. Install NAV 2009 + Web Services components

2. This example has been developed using the following scenario:

- Windows Server 2008 x64

- SQL Server 2005 SP2 x64

- NAV 2009 IT RTM

A. CREATE THE OrderBy TABLE

1. Create a new table (e.g. 50000) and give it the name OrderBy with those fields:

- User ID Text 65 (used to store the USERID)

- Page No. integer (Number of the page published as WS)

- OrderByInt integer (store the number of the key used)

- SortOrder Boolean (TRUE means ASC, FALSE means DESC)

2. Save and compile the table (e.g. 50000 OrderBy)

B. CREATE THE OrderByWS PAGE

1. Create a new page (e.g. 50001) based on the OrderBy table previously created (e.g. Table 50000) and give it the name OrderBy. This page will be published as Web Service and will expose all its own CRUD methods.

- PageType property: Card

- SourceTable property: OrderBy

2. Select all the fields of the table using the classic Field Menu and put all of them into a group:

- "User ID"

- "Page No."

- OrderByInt

-SortOrder

3. Save and Compile (e.g. 50001 OrderBy)

C. CREATE THE CustomerWS PAGE

1. Create a new page (e.g. 50002) based on the Customer table (Table 21) and give it the name CustomerWS. This page will be published as Web Service and will expose all its own CRUD methods.

- PageType property: Card

- SourceTable property: Customer

2. Select the fields reported below by using the classic Field Menu and put all of them into a group

- "No."

- Name

- Address

- "Country/Region Code"

- City

3. Click on View > C/AL Globals > Functions tab

4. Create a new function called SetPageKey

5. Click on Locals button and create a new Local variable for the SetPageKey function:

OrderByRec Record OrderBy

5. Close the C/AL Globals form

6. Edit the C/AL Editor of the page by pressing F9 and start adding C/AL code to your page

7. In the OnInit trigger write this line:

SetPageKey; //Call the SetPageKey function

8. In the SetPageKey function write this sequence of C/AL code:

IF OrderByRec.GET(USERID,50002) THEN BEGIN

CASE OrderByRec.OrderByInt OF

1 : SETCURRENTKEY(Name);

2 : SETCURRENTKEY("Country/Region Code");

ELSE

SETCURRENTKEY("No.");

END;

ASCENDING(OrderByRec.SortOrder);

END;

9. Save and compile the page (e.g. 50002 CustomerWS)

D. PUBLISH YOUR PAGE AS WEB SERVICES

1. Run Table 2000000076 Web Service

2. Insert the following lines

Object Type Service Name Object ID Published

Page Cust WS 50002 Yes

Page OrderBy WS 50001 Yes

3. Once you have ticked the Published control, your page should be published as Web Service in your environment. To verify this you can easily check it out at:

http://<SeverName>:<WebServicePort>/<ServiceName>/ws/<CompanyName>/Services

that in this scenario has to be:

http://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Services

for more information on working with Web Services, please refers to MSDN at:

http://msdn.microsoft.com/en-us/library/dd355036.aspx

E. DESIGN YOUR CUSTOMER WINFORM

1. Open Visual Studio 2008

2. Create a new WinForm VC# project

3. Add Controls to the form:

  a. Button control

    (name) - btnLoadData

    Text - Load Data

  b. 3 RadioButton controls + GroupBox control (Text - Key)

    (name) - rbNo

    Text - Customer No.

    (name) - rbName

    Text - Name

    (name) - rbCountry

    Text - Country

  c. 2 RadioButton controls + GroupBox control (Text - Order)

    (name) -rbAsc

    Text - Ascending

    (name) - rbDesc

    Text - Descending

  d. DataGridView

4. Add Reference to your Web Services.

  a. In the Solution Explorer, right click on References > Add Web Reference

  b. Click on Add Web Reference

  c. Paste the link to the URL related to the Services and click Go:

http://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Services

  d. Click on View Service in the Cust_WS service row and change the Web Reference name to:

CustWRN (customer web reference name)

  e. Click on View Service in the OrderBy_WS service row and change the Web reference name to:

OrderByWRN (OrderBy web reference name)

5. Link the DataGridView to the CustWS service

  a. Click on the right arrow in the upper right position of the DataGridView

  b. In the DataGridView Tasks click on Choose Binding Source

  c. Explode the tree and select CustWRN, a brand new CustWSBindingSource will be created

  d. Return to DataGridView Tasks and untick all the Enable check boxes

  e. Click on Edit Columns and add all the fields present (tip: change some of the column properties in order to let the DataGridView display the data properly), click OK.

F. WRITE C# CODE TO LET THE WINFORM ANIMATE…

Add the C# code into your form to let it work as expected.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Security.Principal; //Namespace useful to retrieve the login

namespace OrderBy

{

//Use 2 Web References based on Order By Page, Customer Page

using OrderByWRN;

using CustWRN;

public partial class Form1 : Form

{

//Declare 2 WS Variables

private Cust_WS_Service CustWSService;

private OrderBy_WS_Service OrderByService;

//Create the integer variables related to the RadioButtons

// for the Order By and the Sorting

private int OptChoice;

private bool SortChoice;

public Form1()

{

InitializeComponent();

//Default Key used = Customer No. (You can always change this code to retrieve

// it from the last access to the WinForm)

OptChoice = 0;

SortChoice = false;

rbNo.Checked = true;

rbAsc.Checked = true;

//Instantiate Customer Page WS and set the correct URL

CustWSService = new Cust_WS_Service();

CustWSService.UseDefaultCredentials = true;

CustWSService.Url = "http://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Page/Cust_WS";

//Instantiate OrderBy page WS and set the correct URL

OrderByService = new OrderBy_WS_Service();

OrderByService.UseDefaultCredentials = true;

OrderByService.Url = "http://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Page/OrderBy_WS";

}

private void rbNo_CheckedChanged(object sender, EventArgs e)

{

OptChoice = 0;

}

private void rbName_CheckedChanged(object sender, EventArgs e)

{

OptChoice = 1;

}

private void rbCountry_CheckedChanged(object sender, EventArgs e)

{

OptChoice = 2;

}

private void rbAsc_CheckedChanged(object sender, EventArgs e)

{

SortChoice = true;

}

private void rbDesc_CheckedChanged(object sender, EventArgs e)

{

SortChoice = false;

}

private void RetrieveRecords()

{

//Create a new Customer recordset

Cust_WS CustomerRset = new Cust_WS();

//Create a new set of filters (empty)

List<Cust_WS_Filter> CustFilters = new List<Cust_WS_Filter>();

//Feed the data grid with the recordset retrieved by the ReadMultiple

// CRUD method on Customer Page and retrieve the first 100 result records

dataGridView1.DataSource = CustWSService.ReadMultiple(CustFilters.ToArray(), null, 100);

}

private void btnLoadData_Click(object sender, EventArgs e)

{

//If the button is clicked perform the following actions:

//Retrieve the login to determine the USERID

AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);

WindowsPrincipal user = (WindowsPrincipal)System.Threading.Thread.CurrentPrincipal;

//Create a new OrderBy recordset

OrderBy_WS OrderByRset = new OrderBy_WS();

//Fill in the new OrderBy recordset with the data read with this key

OrderByRset = OrderByService.Read(

user.Identity.Name.Substring(user.Identity.Name.LastIndexOf('\\') + 1), 50002);

//If there is no record in the OrderBy table then create new one

if (OrderByRset == null)

{

OrderBy_WS OrderByRsetCreate = new OrderBy_WS();

//Set the values for a brand new OrderBy record

OrderByRsetCreate.User_ID = user.Identity.Name.Substring(

user.Identity.Name.LastIndexOf('\\') + 1);

OrderByRsetCreate.Page_NoSpecified = true;

OrderByRsetCreate.Page_No = 50002;

OrderByRsetCreate.OrderByInt = OptChoice;

OrderByRsetCreate.SortOrder = SortChoice;

//Create the NAV OrderBy table record with the key and sort order selected

OrderByService.Create(ref OrderByRsetCreate);

//Flush the OrderBy record after doing the create

OrderByRsetCreate = null;

}

else

{

//Change the value of the fields OrderByInt and SortOrder in the new OrderBy recordset

OrderByRset.OrderByInt = OptChoice;

OrderByRset.SortOrder = SortChoice;

//Update the NAV OrderBy table record with the new key and sort order selected

OrderByService.Update(ref OrderByRset);

}

//Flush the OrderBy record

OrderByRset = null;

//Retrieve the record in the right sort order by using the SETCURRENTKEY statement

// that you find in the Customer Page into the SetPageKey function

RetrieveRecords();

}

}

}

7. Run the WinForm by pressing F5 and play with Key selection and Sort order dynamically with your brand new Customer WinForm!

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

Best Regards,

Duilio Tacconi

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA

Just a small tip that I use often when I have a large SQL Profiler trace. Rather than reading through trace files line by line, you can read it into a table and then query it. This syntax creates a new table in SQL Server and loads your trace file into it:

SELECT * INTO MyTraceTemp

FROM ::fn_trace_gettable('c:\x\MyTrace.trc', default)

The default parameter means that if the trace is across multiple trace files, it will automatically read the next trace file(s) too. If your trace consists of a lot of trace files you can limit how much you read by setting it to for example 3 to just load the first three files.

Once you have the trace file in your table it might be a good idea to add indexes on the table, for example an index for Reads and one for Duration. Then query it, like this for example:

SELECT Reads, Duration, * FROM MyTraceTemp ORDER BY Reads DESC

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

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

Our colleague raised problem: why he receives strange error during report compile, after he changed SourceExpr value for some control in report designer in NAV 2009.

Error message was:

Error while validating RDL content:
The Value expression for textbox “CompanyInfo” refers to the field ‘PricesInclVAT_YesNo’. Report Item expressions can only refers to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Error itself looks very strange because:
1. It comes from validating RDL content, but we changed nothing in it.
2. Changed was SourceExpr in NAV classic client report designer>sections>control property, and it has no relations to field mentioned in error. Actually what was changed: report 204 – Sales-Quote; control 116; SourceExpr had value "Sales Header"."Prices Including VAT" and now it has value FORMAT("Sales Header"."Prices Including VAT").

If we change back value – error disappears.

After deeper analyze we found that RDL dataset has field ‘PricesInclVAT-YesNo’, but after report designer change SourceExpr value by adding FORMAT – this field disappears from dataset. So problem really is in the SourceExpr value, however value itself is correct. And after few more research we found there is control 167 – name PriceIncVAT_YesNo which has exactly the same SourceExpr value - FORMAT("Sales Header"."Prices Including VAT").

Conclusion: only one source expression is pushed to RDL, if there are few controls with the same SourceExpr, only first will be in RDL, other will not appears there.

The field names that we show in the RDL dataset do NOT come from the SourceExpr property "directly". Each control on a report has a DatasetFieldName property, which is what we use to build the field names in the RDL dataset.

The DatasetFieldName property is then derived from the SourceExpr property the first time you open Visual Studio for a report using the View --> Layout menu item. After this, if you change the SourceExpr, the DatasetFieldName does not get changed. (The whole purpose of this property is to fix the dataset field name so even if you change your SourceExpr, you won’t need to redesign your RDL). This should cover most of the user scenarios.

In a few scenarios, you are not covered though: one is, if you move sections or controls in the layout, and if two controls have the same SourceExpr value, the control we choose to export to the report dataset may change with another one with the same SourceExpr. Since they have different DatasetFieldName properties, your RDL will break. We don’t cover this scenario and we don’t believe that we could fix this easily.

So, during report design be very careful with modifying 2 properties: SourceExpr and DataSetFieldName. Changes could impact RDL very much and result could be not you are expecting. Of course this is important if you already have RDL report prepared, if you will create new layout by using "Laoyout suggestion", problems will not come, because new RDl laoyout will be created regarding earlier described rules.

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

Special thanks to John Vestergaard from Logica Danmark A/S for pointing us to this feature.

Gedas Busniauskas

Microsoft Lithuania
Microsoft Customer Service and Support (CSS) EMEA

I keep forgetting where the CustomSettings.config, ClientUserSettings.Config etc files are. So I keep looking it up again in "Where is the zup file". This time, I collected the location of these files in a NAV form so I don't have to look them up anymore. In addition to this you can edit the files from the form directly. It looks like this:

The first time you run the form it will insert a record with the default settings. If your installation is in different folders, then you can of course just modify this. You can also create new records in case you have multiple NAV2009 installations / config files.

Note: If you run on Vista / Win2008 and want to update the config files from here, then you may have to start your NAV client as Administrator, or you may get "access denied" when clicking the update button.

I hope this makes it simpler to change setup settings. Objects attached are:

Instructions for use: Run form 74250. 

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

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

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

Best Regards,

Duilio Tacconi

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA

Special thanks to Kenny Vaes of Helios-IT

Three terms keep coming up when setting up NAV 2009 when NAV-server (middle tier) runs on a different machine than SQL Server:
  - Delegation / Impersonation
  - Kerberos
  - SPN

Delegation / Impersonation is what it says it is. Kerberos is handled more or less automatically by Windows. But what do SPNs actually do....

(un)fortunately SPN can't be explained without first explaining Delegation and Kerberos.

Delegation / Impersonation:
The end-user (RTC) logs on to the NAV-Server which then logs on to the SQL Server - using the end-user's credentials (impersonating the end-

user). All logins here are Windows logins. Impersonation would also be a nice way for a hacker to gain access, so therefore Windows requires

that a domain administrator specifically allows the NAV-server to impersonate users on the SQL Server.

This is only when the NAV-Server and SQL Server runs on two different machines. When they run on the same machine, then Windows has already

authenticated the end-user when they connected to the NAV-Server. So when the NAV-Server logs on to the SQL Server (on the same machine),

Windows has already knows the end-user. So in this case there is no need to set up delegation, and hence no need to worry about Kerberos and

SPNs.

But with two machines, delegation is needed. Delegation requires Kerberos.

Kerberos:
Connections between two users happen all the time. What Kerberos adds to such a connection is a certificate (ticket) which ensures that each

of the two users can trust the identity of the other user. This is actually a kind of countermeasure against a user (hacker) impersonating

another user. Windows requires that before it allows delegation, that the connection being delegated is a Kerberos connection. In this way,

Windows has assurance that the user credentials being forwarded are valid. So when RTC connects to the NAV-server, it has to do this with a

Kerberos connection.

A Kerberos connection is between two Windows user accounts. A user doesn't know (shoulnd't know) what user account the NAV-server runs under.

This is where SPNs come in. So, finally we get to the SPNs:

SPN:
SPN (Service Principal Name) is a simple table that maps a service to a user account. Think of it as a table with two fields: Service-name,

and Windows User name. When the NAV client wants to start a Kerberos connection to the NAV-server, it will connect to for example

DynamicsNAV\Nav-Server:7046. Kerberos requiring a user name will then look this up in the SPN table and find the user name there. So, only if

an SPN has been created for the account that runs the NAV-service, will Windows be able to then start a Kerberos connection. And the user will

never need to know which that account the NAV-server is running under.

When setting up SPNs, make sure that:
  1) The account that runs the NAV-service has an SPN that contains what the NAV client will connect to (Server name in the right format),and
  2) Quite a common probem: Make sure that there are no duplicate SPNs. If you change the NAV-service to run under a different account you must set up SPNs for the new account. Then make sure to also remove the SPNs for the old account.

So in short: Delegation / Impersonation requires Kerberos. Kerberos connection to a service running an unknown account, requires SPN. And,

visa versa, if SPNs have not been set up correctly, then Kerberos won't work so then Delegation wont work either.

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

Best regards

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

Setting up delegation and SPNs for NAV 2009 on three machines (when the NAV Server and SQL Server are on two seperate machines) is described in "Walkthrough: Installing the Three Tiers on Three Computers". But in addition to this, also be aware of the following:

• The syntax of SPNs has changed since KB 968189
• You may need to set UserAccountControl flag in Active Directory

Also thanks to our German colleagues for already posting this information here and here.

Syntax of SPNs has changed since KB 968189

After installing KB 968189 for NAV 2009 (Build 6.0.28795.0) the SPNs now have this format:
<Instance>/<server host>:<port>
<Instance>/<server FQDN>:<port>

So for example if the SPNs used to be set like this:

NAV-SERVER_DynamicsNAV/NAV-SERVER.Domain.com:7046
NAV-SERVER_DynamicsNAV/NAV-SERVER:7046

From this build and later they have to be set like this:
DynamicsNAV/NAV-SERVER.Domain.com:7046
DynamicsNAV/NAV-SERVER:7046

About KB 968189 itself, it solves a very specific problem where it may not be possible to connect if the domain has multiple DNS Suffixes, which is quite unusual. Most installations are not affected by this. You can check whether you are affected by running ipconfig from a command prompt, and see if it lists more than one domain name under "Connection-specific DNS Suffix".

Setting UserAccountControl flag

In some cases you have to set UserAccountControl as described in KB 305144. You set this flag by running ADSIEDIT.msc, go to properties of the user account running your NAV Server, and then select UserAccountControl. Setting it to 17301504 means TRUSTED_FOR_DELEGATION+TRUSTED_TO_AUTH_FOR_DELEGATION

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

Best regards

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA