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

The SQL query below shows a list of tables sorted by the largest tables first. For each table, the indexes in the table are shown. For each index it shows when the index was last used. The query is designed to collect various pieces of information in one place, and give an overview to help supporting decisions when doing index tuning.

The left hand columns show data for the table (No. of receords, data- and index size) to have a view of the impact of having indexes on the table.

The right hand columns show data for each index, including Updates (costs) and Reads (benefits) and when it was last used sine the last time SQL Server was restarted.

Further comments:

• The query only works on SQL Server 2005 and later.
• The numbers in the query are reset every time SQL Server restarts.
• The query may take up to a few minutes to run.
• The query is provided "as is", with no warranties and confers no rights. You assume all risk for your use.

If you have comments or feedback, please feel free to post them here.

Best regards

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

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

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 (

CASE

WHEN (ps.index_id < 2) THEN row_count

ELSE 0

END

) AS [rows],

SUM (ps.reserved_page_count) AS reserved,

SUM (

CASE

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)

END

) 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)

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_UnUsed] UnUsed_Space,

[F_Index_Name] Index_Name,

[F_User_Updates] Index_Updates,

[F_User_Reads] Index_Reads,

[F_Last_Used] Index_Last_Used,

[F_Index_Type] Index_Type,

from z_IUQ2_Temp_Index_Keys

order by F_Row_Count desc, F_Table_Name, [F_Index_ID]

NAV Server (middle tier) utilises Windows Communication Foundations (WCF), which includes a number of performance counters that you can use to monitor NAV Server performance in real-time. This example shows how to monitor "Calls Per Second" for NAV Server to get some idea of how busy it is over a working day / over time. Many other counters are available.

Enable Counters

First, enable WFC Performance Counters as described in this article WCF Performance Counters:

1)  Use Notepad to edit "Microsoft.Dynamics.Nav.Server.exe.config" in the NAV Service folder (default is C:\Program Files\Microsoft Dynamics NAV\60\Service).

2)  Add the section below, marked with Red:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings file="CustomSettings.config" />
  <system.diagnostics>
    <assert assertuienabled="false" />
  </system.diagnostics>

  <system.serviceModel>
      <diagnostics performanceCounters="All" />
  </system.serviceModel>

</configuration>

3)  Restart NAV Server.

Watch Counters

Use Windows Performance Monitor to select and view counters from NAV Server:

1)  Start Windows Performance Monitor (Start -> Run -> Perfmon.exe)

2)  Add a Counter, then from available categories, select ServiceModelService. This will list the NAV Server, so select this. Then add the counter(s) you are interested in - in this example "Calls Per Second":

I found that in a stand-alone situation, the only counter that really shows anything is "Calls Per Second". But in a multi user system, this could well be very different - please share your experiences here.

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

Upgrade procedure to Dynamics NAV 2009 is not very different from upgrade procedures we had in previous versions, but some differences do apply, depending on what we want to achieve with the upgrade.

First scenario is executables only upgrade, which is the same as traditional executables upgrade in NAV. That means upgrading classic NAV client to NAV 2009 version by opening previous database versions with new client, and selecting yes when prompted to convert database, without any object changes or data transfer. Or alternatively, creating a new database with NAV 2009 client, and restoring a backup of customer's database.

This will, as before, upgrade the version of executables running NAV to 2009 version (builds 6.0 and higher) and create necessary system tables, but this will not automatically open up for using new NAV 2009 functionality (like RTC client or Web Services). Running the first scenario is a mandatory part of any upgrade scenario, just as it was in previous versions.

Beware of few issues that can rise from this scenario, see the link below for description.

http://blogs.msdn.com/nav_developer/archive/2009/05/08/upgrading-older-databases-to-nav-2009-runtime.aspx

Another scenario is doing an executable upgrade that would open up for using Web services, but would not include any object upgrade or data transfer.

This is where 3-tier scenario enters stage and a Dynamics NAV server should be set up and connected to the database (this is the normal installation/setup process of the service). Dynamics NAV Web service should also be installed, see Dynamics NAV documentation for installation and setup of different components.

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

To be able to use Web Services after doing an upgrade of executables and setting up a NST and Web service, one must also import and populate web service form (810) from a NAV 2009 client. This will open up for exposing codeunits (and also pages, but at this stage, pages are not yet present in database).

To further enable using RTC, one should also import tables 9050-9060, these are cue tables used in role centers of RTC. If you are creating and running custom Role Center pages, remember to create a record in each table by specifying any value as primary key (or follow code examples from standard Role Center pages that will insert a record if table is empty). A record in any of these tables will be automatically inserted when using standard NAV Role Page (based on that table).

In addition, reports should be 'transformed' to add RDLC layer and forms should be transformed to pages. You can also run reports with classic engine form RTC (that is run classic NAV report in RTC), in which case no object upgrade is necessary for reports. Note though, to run reports with classic engine on RTC client, each machine running RTC client (and classic reports) needs to have Classic client installed as well.

Furthermore, Role Center pages should be created or standard ones imported from NAV 2009 client (pages in range 9000-9020). Finally, PROFILE table (table 2000000072) should be populated with standard (or custom) profiles, and role center pages tied to these.

Last, but not least, you must make sure the existing code is modified to run in both scenarios (see MSDN documentation on code considerations for NAV 2009). 

However, the job needed in this last scenario (specially transforming forms and reports) is not much different than using full database upgrade . In full upgrade version, objects in standard and custom version of NAV (version we are upgrading) would be merged with standard NAV 2009 objects to create custom NAV 2009 database. Note, all forms in standard NAV 2009 version are transformed to pages, so it is advisable to run Form transformation to create pages, once forms are merged to NAV 2009 object version. Also, one should 'upgrade' reports to add RDLC layer, once custom reports are merged to NAV 2009 version.

No data transfer would occur from W1 5.0 to W1 NAV 2009 version, as there are no changes to table structure from 5.0 version. Upgrading from earlier version would still imply data transfer using Upgrade Toolkit.

It's quite normal to upgrade the runtime (executables) to newer versions while leaving the database (application) as it is. This will enable the new runtime features such as Web Services. Recently we have seen a number of questions about whether you can upgrade older databases to NAV 2009. As such, NAV 2009 is just another version, so the default answer would be that yes, you can do that.

Just remember a few additional things to be avare of:

1)  There is one potential issue: NAV 2009 has a change from older versions that comparing characters in a CASE structure is case sensitive. For example:

CodeVar := 'A';

CASE CodeVar OF

  'a':

    MESSAGE('xyz');

END;

In version 5 you will get the message. In NAV 2009 you won't. This has one specific implication in the standard application. Most matrix forms have a function called "DimCodeToOption". As an example is Form 113 Budget. The function has a parameter "DimCode" which in version 5 was type Code. This has to be changed to Text. Otherwise, the case sensitive comparison will fail, and Matrix forms will not show any data. So make this change in Form 113 and other forms, for example Form 554 "Analysis by Dimensions". And if you find other matrix forms have stopped working, then check this first.

2)  If you want to run RTC, then of course you would also have to import pages or run transformation first.

3)  Once you upgrade objects to NAV2009 runtime, you cannot use the objects in previous versions again, even if you export them and import into for example a NAV 5 database. NAV2009 adds new properties to objects which older versions will not be able to handle.

A number of customers have already made the change to NAV 2009 runtime, and to my knowledge no other issues have been reported regarding running the older database in a newer runtime. If you do find any issues not mentioned here, then please do add a comment below,

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

A while a go we posted a blog decribing how to make some basic customizations to style sheets used for export to Excel.

http://blogs.msdn.com/nav_developer/archive/2009/02/18/customizing-style-sheets-for-ms-excel.aspx

In addition to changes described there (customizing font styles, conditional formatting and adding totals), this will allow adding some basic document properties, choosing to display zero decimal fields as blank, and adding header and footer to the exported document. In addition, code changes added here would allow user to run send to excel, apply described customizations directly in exported document, then import the document to automatically generate customized style sheet. The objects are attached.

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

All feedback regarding issues or suggestion to this post are very welcome!  

Jasminka Vukovic, Lars Lohndorf-Larsen

Microsoft Dynamics NO

Microsoft Customer Service and Support (CSS) EMEA

The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled "Cursor Types" on the SE Blog - http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/

However, we are seeing more and more cases where the use of the SQL Index property in NAV 5.0 SP1 is causing performance issues, so the purpose of this blog is to explain in more detail from an application perspective how this property can affect performance.

From version 5.0 we are using Dynamic cursors, which means that SQL server will tend to choose an index that fits the ORDER BY clause more than the WHERE clause. This is not a problem as long as there is no conflict between the ORDER BY and the WHERE clause. This is where the SQL Index property comes in.

SQL server should be able to choose the optimal index IF we create an index which matches the ORDER BY or the filter/range perfectly. NAV constructs the ORDER BY based on the NAV Key specification not the SQL Index specification. If a SQL Index value is specified on the NAV key and the fields do not exactly match the fields and the order in which they were specified in the Key, then there could be a conflict between the ORDER BY and the WHERE clauses.

How does the issue present itself in the application? These are just a couple of examples we have seen of the impact the SQL Index specification can have on the application…

Poor performance on large tables like Ledger Entry tables, if a user selects Sort on the form and changes from the primary key to a secondary key which has a SQL Index specification.

Using the RunFormView and RunFormLink property of a form…If the RunFormView is set to a non-primary key,  SQL might not choose the first key matching the RunFormView if that key has a SQL Index specification. Depending on the keys defined on the table, there may not be another adequate key available, so a suboptimal index may be used. The ORDER BY may no longer match the WHERE clause (RunFormLink), resulting in very poor performance loading the form.

Performance problems when SETCURRENTKEY/SETRANGE is used in code and the key specified has a SQL Index specification that does not match the key or the filters entered (WHERE clause).

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

We are seeing issues mainly with customers who have upgraded to 5.0 SP1 from 5.0, because there were values specified for SQL Index in many tables in the base product in 5.0. Some other customers on pre-5.0 versions might also have issues if someone has done performance tuning on their database. In earlier version of NAV, SQL Index was used to improve performance in certain scenarios. 

The recommendation is that the use of the SQL Index specification should be limited to very specific situations where thorough testing shows performance improvement. In the vast majority of scenarios, SQL will make the correct choice of Index, so the SQL Index specification is not needed and can actually cause poor performance.

For more information on identifying these problematic queries, please see the SE Blog post mentioned at the top of this article.

Laura K. Lake

With the release of 5.0 SP1, a new data type conversion routine was introduced with codeunit 5302 - Outlook Synch. Type Conversion. There are several known issues with data migration due to these changes.

The previously published blog for 5.0 SP1 contains a change log of some code suggestions and workarounds for the more common and/or critical issues. 

This blog addresses many of the same issues in NAV 2009. While some corrections were made between the release of 5.0 SP1 and 2009, not all fixes made it into the RTM release of 2009. The following change log can be applied to the 2009 objects.

This change log applies to 2009 RTM application objects ONLY.

Issues addressed by the change log:

1) Overflow on type conversion from Text to Text in Form 8626 when a text field larger than 100 chars is imported.
2) Error on import of fields beginning with a number -1099 Code, etc.- The call to member selectSingleNode failed. msxml6.dll returned the
   following message: Expression must evaluate to a node-set.
3) Imported dates are blank after migration data is applied.
4) Negative decimal values are converted incorrectly, resulting in changed values.
5) Expected token 'EOF' found '$'. Error on fields with $ symbol

Changes are labeled with the associated number above.

Form 8626 Migration Records

---------------------------

Before:

CODE

  {

    VAR

      MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];

      MigrationData@1001 : Record 8615;

      MatrixCellData@1002 : ARRAY [100] OF Text[100];

      MigrationColumnField@1004 : ARRAY [100] OF Integer;

      MatrixColumnOrdinal@1003 : Integer;

      FormCaption@1005 : Text[1024];

      TableNo@1006 : Integer;

      Text001@1007 : TextConst 'ENU=%1 value ''%2'' does not exist.';

After:

CODE

  {

    VAR

      MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];

      MigrationData@1001 : Record 8615;

      MatrixCellData@1002 : ARRAY [100] OF Text[250];                    // changed #1 - changed from Text[100] to Text[250]

      MigrationColumnField@1004 : ARRAY [100] OF Integer;

      MatrixColumnOrdinal@1003 : Integer;

      FormCaption@1005 : Text[1024];

      TableNo@1006 : Integer;

      Text001@1007 : TextConst 'ENU=%1 value ''%2'' does not exist.';

* XMLport 8610 - Setup DataSchema

---------------------------------

...

NameIn := DELCHR(NameIn,'=','Ù''`');

NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%','            '),'=',' ');

NameIn := DELCHR(NameIn,'=',' ');

EXIT(NameIn);

...

After:

...

NameIn := DELCHR(NameIn,'=','Ù''`');

NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$','             '),'=',' ');  // Changed - added $ symbol and one additional space between single quotes #5

NameIn := DELCHR(NameIn,'=',' ');

IF (NameIn[1] >= '0') AND (NameIn[1] <= '9') THEN                                                      // Added #2

  NameIn := '_' + NameIn;                                                                                                                                     // Added #2

EXIT(NameIn);

* Codeunit 8611\Function FieldNodeExists ...

--------------------------------------------

Before:

FieldNode := RecordNode.selectSingleNode(FieldNodeName);

IF NOT ISCLEAR(FieldNode) THEN

  EXIT(TRUE);

After:

IF (FieldNodeName[1] >= '0') AND (FieldNodeName[1] <= '9') THEN                                       // Added #2

  FieldNodeName := '_' + FieldNodeName;                                                                                                                      // Added #2

FieldNode := RecordNode.selectSingleNode(FieldNodeName);

IF NOT ISCLEAR(FieldNode) THEN

  EXIT(TRUE);

* Codeunit 8611, Function GetElementName

----------------------------------------

Before:

...

NameIn := DELCHR(NameIn,'=','Ù''`');

NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%','            '),'=',' ');

NameIn := DELCHR(NameIn,'=',' ');

EXIT(NameIn);

...

After:

...

NameIn := DELCHR(NameIn,'=','Ù''`');

NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$','             '),'=',' ');  //Changed - added $ symbol and one additional space between single quotes #5

NameIn := DELCHR(NameIn,'=',' ');

IF (NameIn[1] >= '0') AND (NameIn[1] <= '9') THEN                                                      // Added #2

  NameIn := '_' + NameIn;                                                                                                                                     // Added #2

EXIT(NameIn);

* Codeunit 8611, Function InsertRecordWithKeyFields

-----------------------------------------------------

Before:

VAR

      MatrixData@1001 : Record 8615;

      MigrationTableField@1007 : Record 8616;

      OSynchTypeConversion@1008 : Codeunit 5302;

      RecRef1@1006 : RecordRef;

      KeyRef@1003 : KeyRef;

      FieldRef@1004 : FieldRef;

      KeyFieldCount@1005 : Integer;

      ToValidate@1009 : Boolean;

    BEGIN

      MatrixData.SETRANGE(TableID,MatrixRecord.TableID);

      MatrixData.SETRANGE("No.",MatrixRecord."No.");

      KeyRef := RecRef.KEYINDEX(1);

      FOR KeyFieldCount := 1 TO KeyRef.FIELDCOUNT DO BEGIN

        FieldRef := KeyRef.FIELDINDEX(KeyFieldCount);

        ValidationFieldID := FieldRef.NUMBER;

        MatrixData.SETRANGE(FieldID,FieldRef.NUMBER);

        IF MatrixData.FIND('-') THEN BEGIN

          MigrationTableField.GET(MatrixData.TableID,MatrixData.FieldID);

          ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

          OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate);        

        END ELSE

          IF KeyRef.FIELDCOUNT <> 1 THEN BEGIN

            ERROR(STRSUBSTNO(Text000,FieldRef.NAME,RecRef.NAME + ':' + FORMAT(MatrixData."No.")));

          END

      END;

      RecRef1 := RecRef.DUPLICATE;

      IF RecRef1.FIND('=') THEN BEGIN

        RecRef := RecRef1;

        EXIT

      END;

      RecRef.INSERT(NOT CompanySetupRun);

    END;

After:

VAR

      MatrixData@1001 : Record 8615;

      MigrationTableField@1007 : Record 8616;

      OSynchTypeConversion@1008 : Codeunit 5302;

      RecRef1@1006 : RecordRef;

      KeyRef@1003 : KeyRef;

      FieldRef@1004 : FieldRef;

      KeyFieldCount@1005 : Integer;

      ToValidate@1009 : Boolean;

      Field@1010 : Record 2000000041;                            // Added #3

      Datevalue@1011 : Date;                                     // Added #3

   BEGIN

      MatrixData.SETRANGE(TableID,MatrixRecord.TableID);

      MatrixData.SETRANGE("No.",MatrixRecord."No.");

      KeyRef := RecRef.KEYINDEX(1);

      FOR KeyFieldCount := 1 TO KeyRef.FIELDCOUNT DO BEGIN

        FieldRef := KeyRef.FIELDINDEX(KeyFieldCount);

        ValidationFieldID := FieldRef.NUMBER;

        MatrixData.SETRANGE(FieldID,FieldRef.NUMBER);

        IF MatrixData.FIND('-') THEN BEGIN

          MigrationTableField.GET(MatrixData.TableID,MatrixData.FieldID);

          ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

          //OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate);         //  removed #3

          Field.GET(RecRef.NUMBER, FieldRef.NUMBER);                                                   //  Added #3

          IF Field.Type <> Field.Type::Date THEN                                                       //  Added #3

            OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)          //  Added #3

          ELSE BEGIN                                                                                   //  Added #3

            EVALUATE(Datevalue, MatrixData.Value);                                                     //  Added #3

            FieldRef.VALUE := Datevalue;                                                               //  Added #3

          END;                                                                                         //  Added #3

        END ELSE

          IF KeyRef.FIELDCOUNT <> 1 THEN BEGIN

            ERROR(STRSUBSTNO(Text000,FieldRef.NAME,RecRef.NAME + ':' + FORMAT(MatrixData."No.")));

          END

      END;

      RecRef1 := RecRef.DUPLICATE;

      IF RecRef1.FIND('=') THEN BEGIN

        RecRef := RecRef1;

        EXIT

      END;

      RecRef.INSERT(NOT CompanySetupRun);

    END;

* Codeunit 8611, Function ModifyRecordWithOtherFields

-----------------------------------------------------

Before:

VAR

      MatrixData@1002 : Record 8615;

      MigrationTableField@1000 : Record 8616;

      Question@1003 : Record 8612;

      Field@1007 : Record 2000000041;

      MigrationTable@1009 : Record 8613;

      DataTemplateHeader@1011 : Record 8618;

      QuestionnaireMgt@1006 : Codeunit 8610;

      TemplateMgt@1012 : Codeunit 8612;

      OSynchTypeConversion@1014 : Codeunit 5302;

      FieldRef@1001 : FieldRef;

      OptionInt@1008 : Integer;

      DateFormula@1010 : DateFormula;

      ToValidate@1013 : Boolean;

After:

 VAR

      MatrixData@1002 : Record 8615;

      MigrationTableField@1000 : Record 8616;

      Question@1003 : Record 8612;

      Field@1007 : Record 2000000041;

      MigrationTable@1009 : Record 8613;

      DataTemplateHeader@1011 : Record 8618;

      QuestionnaireMgt@1006 : Codeunit 8610;

      TemplateMgt@1012 : Codeunit 8612;

      OSynchTypeConversion@1014 : Codeunit 5302;

      FieldRef@1001 : FieldRef;

      OptionInt@1008 : Integer;

      DateFormula@1010 : DateFormula;

      ToValidate@1013 : Boolean;

      DateValue@1500000 : Date;                   // Added #3

Before:

    IF MigrationTableField.FIND('-') THEN

      IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN

        FieldRef := RecRef.FIELD(MatrixData.FieldID);

        IF CompanySetupRun THEN

          ToValidate:= FALSE

        ELSE

          ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

        OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)

      END;

      IF DataTemplateHeader.GET(MigrationTable."Data Template") THEN

        TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);

After:

    IF MigrationTableField.FIND('-') THEN

      IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN

        FieldRef := RecRef.FIELD(MatrixData.FieldID);

        IF CompanySetupRun THEN

          ToValidate:= FALSE

        ELSE

          ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

        //OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)          // removed #3

        Field.GET(RecRef.NUMBER, FieldRef.NUMBER);                                                   // Added #3

        IF Field.Type <> Field.Type::Date THEN                                                       // Added #3

          OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)          // Added #3

        ELSE BEGIN                                                                                   // Added #3

          EVALUATE(DateValue, MatrixData.Value);                                                     // Added #3

          FieldRef.VALUE := DateValue;                                                               // Added #3

        END;                                                                                         // Added #3

      END;

      IF DataTemplateHeader.GET(MigrationTable."Data Template") THEN

        TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);

* Codeunit 5302, Function TextToDecimal

----------------------------------------

Before:

VAR

      PartArray@1003 : ARRAY [2] OF Text[250];

      IntegeralPart@1001 : Integer;

      FractionalPart@1002 : Integer;

After:

VAR

      PartArray@1003 : ARRAY [2] OF Text[250];

      IntegeralPart@1001 : Integer;

      FractionalPart@1002 : Integer;

      Sign@1005 : Integer;                   //add new local variable #4

Before:

DecVar := IntegeralPart + (FractionalPart / POWER(10,STRLEN(PartArray[2])));    

After:

IF STRPOS(InputText,'-') = 0 THEN     //added #4

  Sign := 1                           //added #4

ELSE                                  //added #4

  Sign := -1;                         //added #4

DecVar := (Sign * (ABS(IntegeralPart) + (FractionalPart / POWER(10,STRLEN(PartArray[2])))));  //changed #4

Laura K. Lake

The virtual table 2000000046 "Automation Server" shows you the physical location of the automation servers (.dll file). But a .net component is not an automation server, so is not listed here. One of the main benefits of .net is exactly to be able to copy and paste files, keep installations simple, and maintain multiple versions. But registering .net components do a lot to hide this simplicity. Windows Explorer also hides the actual structure of the registered .net components.

 To see the real structure, use a command prompt instead. Go to the folder c:\windows\assembly. Windows Explorer Will just list your .net components. But go to the same location from a command prompt, and it is different:

The folder contains sub folders like GAC, GAC_32 and GAC_MSIL. When the .net framework looks for a .net component, it goes into one of these folders - typically GAC_MSIL, but it depends on which version of .net framework is installed.

The next thing the .net framework does when locating a .net component, is to go into the folder with the same name. This folder then contains a folder for each version of this component. If .net is looking for a specific version which does not exist, then if you create a new folder here with this version, then .net will look in this folder. Finally, here you have the actual .net component (dll file) which the .net framework will 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.

In classic reports, adding a header to your report is as easy as just copying the Header section from an existing one.

With reports for RTC you need to do a bit of manual work to get your report header. A standard header contains the following information:

Report Name

Company Name

Time and date (=Globals!ExecutionTime)

Page Caption and Number (=Globals!PageNumber)

User ID (=User!UserID)

Time and Date, Page Number and User ID are automatically defined as global variables in Visual Studio report designer. All you need to do, is: Add a TextBox, right click on it and select "Expression...", then select from the Globals-Menu:

The other fields (Report Name, Company Name and Page Caption) are labels, and have to be added following the steps described here:

Labels in NAV2009 Report Design

So these are the two ways (Globals and Labels) that you have to add information to your report headers as illustrated 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.

When adding fields to a report, the caption is typically added automatically or with a bit of drag and drop from the DataSet in VS Report Layout. But if you want to add additional labels, for example Report Name in the header, then there are a few more steps you must follow.

Of course, you can just add a new TextBox in VS Layout, and type in the value you want to show. This is simple but will not give you Multi Language. Using a label from the classic report design gives you Multi language, but is a bit more complicated. This is what you need to do:

As usual, before you can use it in the layout you must add the label to the report sections, and then specify the caption you want to display. In this case, make sure to also specify a name in the DataSetFieldName-property of the label. For this example, let’s call it Report_Label.
To get the label value (Caption) into the layout, you must first add it to the Table on the Body-part of the layout. Note: It must be part of the table, and not in a TextBox outside of the table. The name of the cell will default to the DataSetFieldName you specified (Report_Label). And the value must be =Fields!Report_Label.Value. You get this value automatically if you drag it from the Data Set into a detail-section of the table. The caption is only here to be available from the header part of the report, so you can set the Visible-property to No.

Finally you can get the label from the table and add it to a header-section in the report layout: Add a new TextBox, then name it something unique, for example Report_Label1, and set Expression = =ReportItems!Report_Label.Value. Note that "Report_Label" refers to the cell you added above, not directly to the label on the sections.

This picture shows the steps needed to get the label to show in the header:

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.