April, 2008

  • Microsoft Dynamics NAV Team Blog

    Using Web Services to Access Microsoft Dynamics NAV 5.0

    • 49 Comments

    Introduction

    Dynamics NAV 2009 contains a new subsystem for dealing with Web Services. This feature has been well received by partners and customers alike. Partners have expressed interest in having web services available for earlier versions of Dynamics NAV. This feedback resulted in a technology talk at Directions2007 in Florida, where the topic was what could be done to day. The conclusion of the talk was that everything we where intending to deliver was already possible today, yes some code is needed but strictly from function/feature perspective all of it is possible, and it is not even all that ugly. Dynamics NAV 2009 will provide out-of-the-box programmatic web service access to the application and will therefore remove the need for this additional technology plumbing described here.

    I have to say that the response to my talk has been tremendous. After the response to my talk on Web Services in NAV 5.0 and previous versions I decided to write this blog post and make the source files available.

    This post is about how to bridge the gap between the need for web services now and the current platform, it will help you understand how you can provide Web Services directly from Dynamics NAV today, in a “simple” and flexible way, already today.

    To work with the samples in this post you will need: Visual Studio 2005, Dynamics NAV 5.0 and .Net 3.0 installed on your system. This sample should work on Dynamics NAV 4.0 to but has not been tested on that version.

    Architecture

    The system we will build contains 4 different components/moving parts: Web Service Listener, Event Dispatcher, Codeunit Eventhandler and XMLPort for stream handling.

    image

    Web Service Consumer

    Any client that understands how to communicate with Web Services; like InfoPath, Visual Studio, SharePoint or any custom application written by you.

    Port

    Is the physical communication port that the WCF listens to.

    WCF Web Service

    Defines the data contracts and service contracts for the Web Service, it also implements the concrete service and opens for listening in the WCF subsystem, it then delegates the requests to the COM Event Dispatcher component.

    COM Event Dispatcher

    This component provides the hookups for Dynamics NAV, both to activate the service and to register event sinks. It defines 2 IDispatch interfaces the IServiceEvents and the IWebServiceListner, as well as the concrete implementation of the IWebServiceListner in the WebServiceListner class that provides the actual code for hooking up the WCF Web Service to Dynamics NAV.

    .NET

    We are using the CLR runtime for writing our Web Service component and our COM plugin. Some of this blog entry is about interop between Dynamics NAV and .NET through COM.

    Codeunit Event Handler

    Is responsible for starting up the WCF Web Service through the COM interface, it then registered for events coming from the WCF Web Service Component. The events routed to XMLPort for processing.

    XMLPorts for datastreams

    It deals with the actual business logic and data coming from or going to the Web Service.

    Implementation

    The implementation is in 2 programming languages: C# and C/AL.

    Please take a look at the provided code sample, for the rest of the information contained in the posting. It can be found here: http://code.msdn.microsoft.com/nav/Release/ProjectReleases.aspx?ReleaseId=896

    I have included comments in the code that should explain what is going on, if you feel something is missing, first look at the documentation for the WCF or post a comment to this post and I will try to answer it.

    Deployment of Sample

    To deploy the sample you will first have to download it, unpack it.

    Then open it up with Visual Studio and compile.

    Then import the codeunit.txt and xmlport.txt into your NAV installation and compile those objects, starting with the XMLPort

    To run the service simply open the Object Designer in NAV, find the Codeunit that you just imported and press run.

    There is no dependency on IIS or other external components. No further deployment steps should be needed.

    In the Visual studio solution is a ConsoleTestApp project. After you have followed the steps above you can run that project, it will test if your install was successful, as well as provide sample on how to use the web service.

    Special considerations

    In this sample I’m using XMLPort to handle the XML stream that is provided.

    You can take many different approaches to this, and still reuse large please of the code provided in the sample.

    To use the XMLPort as handler you will have to set the encoding property to UTF-8. This is due to a null termination bug in stream handler in NAV.

    image

    image

    With this approach you can already today, incorporate web services in your projects in straightforward way.

    The appropriate usage is whenever you need to give external application access to Dynamics NAV data or business process.

    For any questions or comments please feel free to ask them in the comment section of this blog post.  I will answer questions to best of my ability on this post in the comments section as well.

    One last thing:  This is a sample code.  It has not been tested, you should thoroughly test this code before usage.

    Best regards,
    Kris Rafnsson

  • Microsoft Dynamics NAV Team Blog

    Importing and exporting NAV objects with C/Front

    • 0 Comments

    This post describes how you can automate importing and exporting of Microsoft Dynamics NAV objects with C/Front.

    To make a basic C/Front project, see the post Beginners guide: How connect to NAV from a .net project, using C/Front. This post only describes the additional code needed to import or export NAV objects.

    Exporting objects:

    The line below will export Tables 1..10 to the file "C:\x\ExportFob.fob":

    CFrontDotNet.Instance.ExportFOB("C:\\x\\ExportFob.fob","WHERE(Type=CONST(Table),ID=FILTER(1..10))");

    The filter has to be in the same syntax as used in the "DataItemTableView" in a report. The simplest way to create the filter is to make a new report in NAV, based on the Object Table. Then in the DataItem Properties, specify a filter in DataItemTableView, and copy that into your C/Front code.

    The file that you export is a normal .fob file, which can be imported back in the usual way (File -> Import from Object Designer), or via C/Front as described next.

    Importing objects:

    C/Front has a special datatype "NavisionImportMode" to control the actions when the objects you import conflict with existing objects. The possible settings are Overwrite, Skip and ThrowError. The code needed to re-import the object we exported before, is:

    NavisionImportMode ImportMode;

    ImportMode = NavisionImportMode.Overwrite;

    CFrontDotNet.Instance.ImportFOB("C:\\x\\ExportFob.fob",ImportMode);

     

    Comments:

    You can only export objects as binary .fob files. It is not possible to export objects as text.

    In SP1 for NAV version 5, C/Front will no longer check for license permissions when importing files. So the import-functionality works just like importing objects manually, which means that the license file is not checked. In previous versions it was not possible to import new objects or tables with new fields unless the NAV license would allow the user to create those objects or fields manually.

    Lars Lohndorf-Larsen (Lohndorf)
    Microsoft Dynamics UK

  • Microsoft Dynamics NAV Team Blog

    Beginners guide: How connect to NAV from a .net project, using C/Front

    • 0 Comments

    Introduction: 

    This post describes the simplest possible way to get started with creating a .net project in Microsoft Visual Studio, and use C/Front in c# code to connecct to a Microsoft Dynamics NAV database. The example here is based on the sample from the product CD, which will install to this folder: C:\Program Files\Microsoft Dynamics NAV\SDK\CFRONT\DotNetSampleApplication. More details can be seen from that sample, and I will also make further blog posts to describe further use of C/Front. For this post, all we want to do is to make a connection.

    The example here was made in Visual Studio 2008. You can do the same in Visual Studio 2005. The NAV database in this example is running on SQL Server. To make it connect to a Native NAV database you nee to adjust NetType and DriverType.

    Time to complete: Approximately 20 minutes.


    Example:

    C/Front is part of SDK (Software Developers Kit), which also includes the Communication Components. If you haven't installed it already, then install SDK from the NAV product CD. Then follow the steps below:

    1)  Open Microsoft visual Studio 2005/2008

    2)  Create a new project (File -> New -> Project). As project type, select "Visual c#", then select "Windows Forms Application". Type in a name and click OK.

    3)  Include C/Front to your project by rightclicking on "References" on the right hand side, under "Solution Explorer", and select "Add Reference". Go to the Browse-tab, and select Microsoft.Navision.CFront.CFrontDotNet.dll. As default this is installed in this folder: C:\Program Files\Microsoft Dynamics NAV\SDK\CFRONT\.

    4)  Now, add a button to your Form (View -> Toolbox, then double-click a button to add it). Also add a TextBox so we have a place to display data from C/Front. Double-click on the button to get to the code.

    5)  At the top of the code-window where Visual Studio has already added a number of "using"-commands, also add a line for C/Front: using Microsoft.Navision.CFront;

    6)  All the preparations done, we are now ready to program with C/Front. Go back down the the function "private void button1_Click", and copy in the code below. Just adjust values NavisionPath, ServerName and CompanyName to match your system:

    // Declare specific C/Front variables

    NavisionNetType NetType;

    NavisionDriverType DriverType;

    NavisionValue Data;

    // Set Net Type and Driver Type (SQL or native)

    DriverType = NavisionDriverType.Sql;

    NetType = NavisionNetType.SqlDefault;

    CFrontDotNet.DriverType = DriverType;

    int CT; // Currentt Table. Keep name short, since we need to type it in a lot

    int CR; // CurrentRecord. Keep name short, as above

    CFrontDotNet.NavisionPath = "C:\\Clients\\W15SP1RTM\\";

    CFrontDotNet.Instance.ConnectServerAndOpenDatabase("Lohndorf1", NetType, "W1500SP1", 2000, true, true, "", "");

    CFrontDotNet.Instance.OpenCompany("CRONUS International Ltd.");

    CT = CFrontDotNet.Instance.OpenTable(18);

    CR = CFrontDotNet.Instance.AllocRecord(CT);

    CFrontDotNet.Instance.FindFirstRecord(CT, CR);

    Data = CFrontDotNet.Instance.GetFieldData(CT, CR, 2);

    NavisionText NAVText;

    NAVText = Data;

    textBox1.Text = NAVText.ToString();

    // Important: Close down Navision when finished.

    CFrontDotNet.Instance.FreeRecord(CR);

    CFrontDotNet.Instance.CloseTable(CT);

    CFrontDotNet.Instance.CloseCompany();

    CFrontDotNet.Instance.CloseDatabase();

    CFrontDotNet.Instance.DisconnectServer();

    CFrontDotNet.Instance.Dispose();

     

    7)  Save the project. As default it will save to this folder: C:\Users\[UserName]\Documents\Visual Studio 2008\Projects\CFrontSampleVS2008\[ProjectName]

    8)  Finally, copy the files CFront.dll and CFrontSQL.dll from the C/Front folder (default C:\Program Files\Microsoft Dynamics NAV\SDK\CFRONT) into the project sub-folder bin\Debug


    Now try to run it. Build the project first (Buld -> Build [ProjectName]), and check that it builds without errors. Then run it (Debug -> Start Debugging - F5). If succesful, it will display the customer name of the first customer on the form.

    Comments:

    Always set the NavisionPath and point it to a NAV client folder of the same version as C/Front. This is where C/Front will locate essential files like dlls, flf and stx files. If you don't set this path, you are likely to get error 2 in module 1 ("The system cannot find the file specified.").

    C/Front requires granule 1,800 C/FRONT. It connects just like a normal client, so it will also consume a normal user session while connected.

     


    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

  • Microsoft Dynamics NAV Team Blog

    Dynamics NAV 5.0 and style sheet issues, tab captions with local characters

    • 0 Comments

    Form tab captions that contain country specific characters (for example, Å in Swedish)  will be displayed differently in MS Word/Excel, when using local language version of MS Office. For example, if running Nav 5.0 SE version and Customer card the first tab on the card (General) will be displayed as Allmõnt instead of Allmänt when sent to Word (when using local MS Office version).

     

    A workaround is suggested here for both Word and Excel. To correct this, a character translation is needed, based on conversion of decimal codes of characters exported. The following example illustrates how this could be corrected.

     

    Note, decimal character codes used in this example are character codes for characters õ and ä described in the scenario

    õ ansi decimal code 143

    ä ansi decimal code 197

    and can be found in ANSI character set table referred to at the bottom of the blog. 

     

    For style sheets used for Word documents:

     

    Open the style sheet file in notepad, default file is NavisionFormToWord, placed in Stylesheet folder of the Client folder.

    Browse to the following section and replace the section

     

                            </w:wordDocument>

                </xsl:template>

                <xsl:template name="output-fonts">

     

    with

                            </w:wordDocument>

                </xsl:template>

    <xsl:variable name="nbsp1">&#143;</xsl:variable>  <!-- this is ANSI code for character as displayed in Word-->

    <xsl:variable name="nbsp2">&#197;</xsl:variable>  <!-- this is ANSI code for character, as seen on Nav form-->

    <xsl:template name="output-fonts">

     

    Then browse to the following section

     

          <xsl:template match="Control[@type='TabPage']">

     

    find the first occurance of the following line and replace the line

     

                            <xsl:value-of select="@caption"/>

     

    With

     

              <xsl:value-of select="translate(@caption,$nbsp1,$nbsp2)"/>

     

     

                For style sheets used for Excel documents:

     

    Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.

    Browse to the following section and replace the section

     

      <xsl:variable name="DecimalSeparator" select="Object/DecimalSeparator"/>

       With

      

      <xsl:variable name="DecimalSeparator" select="Object/DecimalSeparator"/>

     <xsl:variable name="nbsp1">&#143;</xsl:variable>

     <xsl:variable name="nbsp2">&#197;</xsl:variable>

     

     

      Then browse to the following section and replace the section

               

    <xsl:template match="Control[@type='TabPage']">

                <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet">

                                        <xsl:attribute name="ss:Name">

                                                    <xsl:value-of select="@caption"/>

                                        </xsl:attribute>

     

    with

     

    <xsl:template match="Control[@type='TabPage']">

    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet">

                            <xsl:attribute name="ss:Name">

                                        <xsl:value-of select="translate(@caption,$nbsp1,$nbsp2)"/>

                            </xsl:attribute>

     

      Finally, find the following section and replace the section

     

                           <Cell ss:StyleID="tabCaption">
                                                   <Data ss:Type="String">
                                                               <xsl:value-of select="@caption"/>
                                                   </Data>
                                       </Cell> 

     

      With

     

                           <Cell ss:StyleID="tabCaption">
                                                   <Data ss:Type="String">
                                                                 <xsl:value-of select="translate(@caption,$nbsp1,$nbsp2)"/>
                                                   </Data>
                                       </Cell> 

     

                 

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

     

    Note that a variable can be added for each character this applies to.

    So to correct 2 such characters one can for example add 4 variables (one for ansi code of each character as shown in Nav and Office).  

     

     <xsl:variable name="nbsp1">&#247;</xsl:variable>

     <xsl:variable name="nbsp2">&#246;</xsl:variable>

     <xsl:variable name="nbsp3">&#143;</xsl:variable>

     <xsl:variable name="nbsp4">&#197;</xsl:variable>

     

    And translate functions can be nested,  so the line

     

                            <xsl:value-of select="@caption"/>

     

    is then replaced with

     

      <xsl:value-of select="translate(translate(@caption,$nbsp1,$nbsp2),$nbsp3,$nbsp4)"/>

     

     

    The following link contains character table for ANSI.

                http://msdn2.microsoft.com/en-us/library/aa771694.aspx

     

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

     

    Jasminka Vukovic (jvukovic )

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

  • Microsoft Dynamics NAV Team Blog

    How to configure and make a table available on Employee Portal in Microsoft Dynamics NAV

    • 0 Comments

    This article describes how to do the following:
    • How to configure a table in Microsoft Dynamics NAV 4.0.

    • How to make the table available in Employee Portal in Microsoft Dynamics NAV. 


    This article describes how to do this on a computer that is running Microsoft Windows SharePoint Services.

     

    Typically, Employee Portal has only some tables configured for the demonstration site. These tables include the following tables:
    • Customer

    • Item

    • Production BOM

    • Sales Header

    • Vendor

     

    Note: This article uses the Job (167) table to show you how to add a table to Employee Portal.

     

    To configure a table in Microsoft Dynamics NAV and then make the table available in Employee Portal, follow these steps:

    Step 1: Create a new Web part request template card

    1. Click Administration, expand Application Setup, expand Employee Portal, and then click Web Part Request Template.
     

    2. Press F3. In the JOBCARD - EP WP Request Template Card dialog box, specify the following settings for the Job (167) table:
    a. In the Code box, type JOBCARD.

    b. In the Description box, type Job Card.

    c. Click to select the Editable check box.

    d. In the Type list, click Card.

    Note: The settings that you specify depend on the table that you are configuring.
    e. In the Caption field, type Job Card.

    f. In the Table No. field, click the lookup arrow, and then click the Job (167) table.

    g. In the Header or Line field, click Header.

    h. If you want to populate the Web part with the first record from the table, click 1 in the First Record Shown field.

    i. In the No. of Records field, click the number of records that you want to display in the Web part.

     

    3. Click Table, and then click Card. Or, press SHIFT+F5.


    4. In the JOBCARD - EP WP Table Card dialog box, follow these steps in the Code field for the General row:
    a. In the Header or Line field, click Header.

    b. In the Caption list, click General.

     

    5. Click Tab, and then click Card. Or, press SHIFT+F5. 


    6. In the JOBCARD 167 Header - EP WP Table Tab Card dialog box, follow these steps:
    a. Click the lookup arrow for the Field No field. 

    b. In the Length field, enter the length for each column.

    c. In the Appearance field, click one of the following items for each column:• Visible 

    • Editable

    • Hidden or Editable 

    d. In the HTML Layout field, click the type of control to which each field belongs. For example, click Text Field, List, or Check Box.

    e. Click to select the Required check box for the required field.

    f. If you want to sort the table by a specific field, click to select the Sortable check box.

    g. If you want to be able to populate a field by using information from another table, select that field in the table. Next, click the Field button, and then click Lookups. Then, click the table that you want to use to populate the field.

     

    7. You can customize tabs such as the POSTING tab and the DURATION tab by following step 1 through step 6.

    You can now use the new Web part request template card together with existing groups. Or, you can create a new group. The new Web part request template card contains a table and its required columns.


    Step 2: Add the Web part request template card to a group

    1. Click Administration, expand Application Setup, expand Employee Portal, click Group, and then click SALES. 

    2. In the SALES - EP Group Card dialog box, click the newly created JOBCARD card in the list of Web part request templates.

     

    Notes
    • Typically, you add the Web part request template card to a group so that the Web part request template card is available on the Web site. 

    • You can add the same card to multiple groups.

    • You can move between groups by using the Next arrow or by using the Previous arrow on the toolbar.


     

    Step 3: Assign the group to a user

    A user who has access to the Sales group will also have access to the JOBCARD card that is created and that is added to the group. Otherwise, you must create a new user.



    Step 4: Add the Web part to Windows SharePoint Services

    1. Open Employee Portal in a Web browser.

    2. In the Add Web Parts list, drag the Card Web Part Web part to the location that you want in the window.

    3. In the Modify My Web Part box, click Sales in the Group list.

    4. In the Web Part Request list, click Job Card, and then click OK. Then, review the Job Card dialog box to make sure that all the fields and all the tabs that you configured are displayed.


     

    Mohamad Vajid (mvajid)
    Microsoft Dynamics ME
    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Dynamics NAV 5.0 and Style Sheet issues

    • 0 Comments

    This post is starting of a summary of style sheet issues reported in Dynamics NAV 5.0 when using send-to Excel/Word functionality, and attempt to provide workaround suggestions for as many of these issues as possible.

    Most issues are related to regional settings and use of decimal separator and or/date format. In 5.0, when xml file with data is created by application, only values displayed on form are passed, along with the decimal separator sign used. Style sheets are determining the field type based on the format of the field value. As different date formats are used in different places, along with different decimal separator/thousand delimiter combinations, this can lead to number of issues. In newer versions, 'data type' attribute is passed along with decimal separator and field value, to avoid ambiguous formatting issues.

    Keep in mind that matrix forms and forms like Customer Sales History are not supported by style sheets at all.

    The list below contains three of the issues reported on 5.0, some corrected in 5.0 SP1 version, and a suggestion for possible workaround.

    Other reported issues will be handled in coming blogs.

    1.      When running forms that contain several subforms (example form 521, Application Worksheet), and selecting Send to Excel, the following error is reported in excel:

              Problems came up in the following area during load: Workbook Setting...

    Table box names created in excel should be added a counter value to the name to avoid this issue. To do this:           

    Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.

    Browse to the following section and replace the marked line :  

               <xsl:template match="Control[@type='TableBox']">
                <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet">
                 <xsl:attribute name="ss:Name">
                  <xsl:variable name="TableBoxCaption">
                   <xsl:value-of select="//Object/@caption"/> 
                  </xsl:variable>
                  <xsl:value-of select="substring($TableBoxCaption,1,30)"/>     <-- REPLACE THIS LINE -->
                 </xsl:attribute>

                with the following line:

                  <xsl:value-of select="substring($TableBoxCaption,1,28)"/><xsl:value-of select="position()"/>

     

    2.       Error when exporting date fields to excel.

    If a form contains date fields and date format is dd.mm.yy or dd.mm.yyyy , following error occurs when running send-to excel, with Decimal Separator equals ‘.’:

              Problems came up in the following area during load: Table...

    If value of decimal separator is '.', style sheet interprets all fields containing numeric values (only) and '.' as decimals. Dates are then      interpreted as invalid decimal values. One way of solving this in 5.0 is to check for number of dots, and number of places between the dots, 2 implying the value is a date. To do this:

    1. Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.
    2. Browse to the following section and replace the section:  

         

    <xsl:when test="$DecimalSeparator = '.'">

                    <xsl:attribute name="ss:Type">Number</xsl:attribute>

                    <xsl:value-of select="translate(@value,',','')"/>

                  </xsl:when>

                  <xsl:when test="$DecimalSeparator = ','">

                    <xsl:attribute name="ss:Type">Number</xsl:attribute>

                    <xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>

                  </xsl:when>

               

    With

     <xsl:when test="$DecimalSeparator = '.'">

    <xsl:choose>   

                            <xsl:when test="string-length(substring-after(substring-after(@value,'.'),'.'))=2">

                                                    <xsl:attribute name="ss:Type">String</xsl:attribute>                                                                   <xsl:value-of select="@value"/>            

                            </xsl:when>     

    <xsl:otherwise>

                                        <xsl:attribute name="ss:Type">Number</xsl:attribute>

                                                        <xsl:value-of select="translate(@value,',','')"/>

                            </xsl:otherwise>

                </xsl:choose>

       </xsl:when>

       <xsl:when test="$DecimalSeparator = ','">

                    <xsl:attribute name="ss:Type">Number</xsl:attribute>

                    <xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>

        </xsl:when>

     

     

    3.      Date fields exported as decimals when Decimal Separator equals ‘,’.

    The same method of workaround could be applied here as for the previous issue. Checking for number of dots and number of digits between the dots and reading value 2 would normally imply that the value is a date.

     

    1.      Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.

    2.      Browse to the following section and replace the section:  

     

    <xsl:when test="$DecimalSeparator = '.'">

                    <xsl:attribute name="ss:Type">Number</xsl:attribute>

                    <xsl:value-of select="translate(@value,',','')"/>

                  </xsl:when>

                  <xsl:when test="$DecimalSeparator = ','">

                    <xsl:attribute name="ss:Type">Number</xsl:attribute>

                    <xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>

                  </xsl:when>

               

    With

     <xsl:when test="$DecimalSeparator = '.'">

                            <xsl:attribute name="ss:Type">Number</xsl:attribute>

                            <xsl:value-of select="translate(@value,',','')"/>

      </xsl:when>

        <xsl:when test="$DecimalSeparator = ','">

    <xsl:choose>   

                            <xsl:when test="string-length(substring-after(substring-after(@value,'.'),'.'))=2">

                                                    <xsl:attribute name="ss:Type">String</xsl:attribute>                                                                   <xsl:value-of select="@value"/>            

                            </xsl:when>     

    <xsl:otherwise>

                                        <xsl:attribute name="ss:Type">Number</xsl:attribute>

                                                       <xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>

                            </xsl:otherwise>

                </xsl:choose>

          </xsl:when>

     

    Jasminka Vukovic (jvukovic )

    Microsoft Dynamics NO

  • Microsoft Dynamics NAV Team Blog

    SQLIndex property

    • 0 Comments

    In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance. In this blog I

    describe what to be careful about, and why the use of this property has been removed in the NAV 5 SP1 application.


    The property is still available and it is still a valuable tool in performance tuning. But from SP1, it is not used anymore

    in the standard application. The document "Changes in NAV 5 0 SP1.doc" on the SP1 product CD lists the 72 tables where the

    SQLIndex has been removed (Change A222).

     

    Background:
    The key-property SQLIndex was introduced in NAV version 4 SP1. The idea of the property is to make it possible to change

    the index on the SQL Server while maintaining the same application (same sorting) in NAV.

    The main use of the property is to make the SQL index more selective. In the NAV application there are many keys that begin

    with an option which is not very selective, for example the primary key "Document Type","No." on the Sales Header table.

    "Document Type" - having only 6 possible options - is not very selective, and SQL Server might choose not to use it. If the

    index was changed to be the other way around ("No.","Document Type"), it would be much more selective and more effecient

    for SQL Server to use in SELECT statements.

    The other benefit of this property is to enable "covering indexes", so that you can have a few indexes to cover for most

    searches, and then disable the maintenance of other indexes. For example an index on the "Sales Header" table beginning

    with "No." can be used effeciently with many different filters, reducing the need to have one key for every possible exact filter.

     

    Problems:
    When you have a query which includes an "ORDER BY"-clause, SQL Server has to return the reords in the order specified by

    that clause. If SQL Server doesn't have a matching index, it has to retrieve data using a different index and then do some

    internal sorting to return data in the correct order. If there are no good indexes, then SQL Server may choose to

    use the clustered index which can be bad enough. But when the query also has an index hint, then SQL Server is forced to use the

    index specified by the hint, and this can lead to large amounts of reads.

     

    Example:
    In a recent support case, the customer had generally bad performance. In this case, the "SELECT TOP 30"-query from the post "Simple query to check the recent performance history" showed that out of the top 30 "worst" queries, 26 were similar to this one


    SELECT TOP 501 * FROM "Reservation Entry" WITH (UPDLOCK, INDEX("$1"))   WHERE (("Source ID"=@P1)) AND (("Source Ref_

    No_"=@P2)) AND (("Source Type"=@P3)) AND (("Source Subtype"=@P4)) AND (("Source Batch Name"=@P5)) AND (("Source Prod_ Order

    Line"=@P6)) AND (("Reservation Status"=@P7)) ORDER BY "Source ID","Source Ref_ No_","Source Type","Source Subtype","Source

    Batch Name","Source Prod_ Order Line","Reservation Status","Shipment Date","Expected Receipt Date","Entry No_","Positive"

     


    The query itself looks good enough: WHERE-clause and "ORDER BY"-clause match each other, and there were no immediate

    reasons why this query should cause more reads than the number of records in the table. But in the standard application, the SQLIndex property for this key was:

    "Source ID","Entry No.",Positive


    The idea with this is SQLIndex is to have an index which can cover more situations, and in that way reduce the number of indexes that

    need to be maintained on SQLServer. And the SQLindex is fine for the SELECT-part of the query. The problem is, that the

    index can't be used for the "ORDER BY"-part of the query.

    So what happens, is:

    SQL Server may have planned to use the clustered index to read all data and then do some internal sorting. But in this

    case, the Index Hint forces SQL Server away from that plan. The result is that SQL Server is forced into doing a very

    difficult task while being restricted by the index speicifed by the index hint.

    In this case we designed the "Reservation Entry"-table and removed the SQLIndex property from the key, and performance went

    up.

     


    Conclusion:
    When you see a query which causes many reads, even if SQL Server has a good index, then also consider if the index is

    good for the "ORDER BY"-part of the query. The "ORDER BY"-part of the query depends on the key in NAV. But if the SQLIndex

    property has been set for this key, then by definition, the "ORDER BY" and the SQL index will not be matching.

    You should still consider the use of the SQLIndex property as part of tuning performance of a system. But just be aware that it can also cause problems as described here.

     

    Lars Lohndorf-Larsen (Lohndorf)

    Escalation Engineer

  • Microsoft Dynamics NAV Team Blog

    How to configure and make a report available on Employee Portal in Microsoft Dynamics NAV

    • 0 Comments

    This article describes the following procedures:
    • How to add a non-predefined report in Microsoft Dynamics NAV

    • How to make the report available in Employee Portal in Microsoft Dynamics NAV


    This article describes these procedures on a computer that is running Microsoft Windows SharePoint Services.

     

    Typically, only some tables are configured for the demonstration site in Employee Portal. These tables include the following tables:
    • Customer

    • Item

    • Sales Header

    • Vendor


    The following reports are available to be added to Employee Portal:
    • Sales Quote (204)

    • Order Confirmation (205)

    • Quantity Explosion of BOM (99000753)

    • Rolled-up Cost Shares (99000754)

    • Detailed Calculation (99000756)


    Note: This article uses the Service Item table (5940) to show you how to add the "Service Item - Resource Usage" report (5939) to Employee Portal.

     

    How to add a non-predefined report:

    To add the "Service Item - Resource Usage" report (5939) to Employee Portal by using the Service Item table (5940), follow these steps:

    1. Add a report that is not related to the predefined tables. To do this, follow these steps:
    a. On the Tools menu, click Object Designer. 

    b. In Object Designer, click Codeunit, select codeunit 6828, and then click Design.  

    c. On the View menu, click C/AL Globals, and then add the new ServiceTableBased function.

    d. Add the following new global variable:
    • Name: Service Item

    • Data Type: Record

    • Subtype: Service Item
     

    e. Add the following line of code to the ServiceTableBased function.

    "Service Item".SETVIEW(PrintRecordRef.GETVIEW);

    ReturnValue := REPORT.SAVEASXML(ReportID,TempPath,TRUE,"Service Item");

     

    f. Add the following line of code to the CreateReportPreview function.

    REPORT::"Service Item - Resource Usage":

    ReturnValue := ServiceTableBased(ReportID,TmpFileName,PrintRecordRef);

     

    g. Click Save, and then click Compile.


    How to make the report available in Employee Portal:

    1. Add the Show Report action to the Action list of the Web part. To do this, follow these steps:
    a. Click Administration, expand Application Setup, expand Employee Portal, and then click Groups.

    b. Select an existing "Web part request" card, and then click Card.

    c. Click Table, and then click Card. Or, press SHIFT+F5. 

    d. Select the required table, and then click Actions.

    e. In the EP WP Table Card dialog box, specify the following information:
    • Type: Show Report

    • Target WP Request: Current

    • Code: SHOWREPORT

    • Target Table No.: 5940

    • Report ID: 5939

    • In the Caption field, type a new caption for the "Service Item - Resource Usage" report.

      

    2. Add the Web part to Windows SharePoint Services. To do this, follow these steps:
    a. Open Employee Portal in a Web browser. 

    b. In the Add Web Parts list, drag a Web part to the location that you want in the window.

    c. In the Modify My Web Part box, select the group that contains the Web part.

    d. In the Web Part Request list, select the Web part for which you added the actions in step 1 earlier in this section.

     

    Mohamad Vajid (mvajid)

    Microsoft Dynamics ME

    Microsoft Customer Service and Support (CSS) EMEA

     

  • Microsoft Dynamics NAV Team Blog

    Bulk Inserts in Microsoft Dynamics NAV 5.0 SP1

    • 0 Comments

    Bulk Inserts is a new feature in NAV 5.0 SP1 which is designed to improve performance when inserting multiple records by delaying the physical inserts on the SQL Server.

    Before this change, inserts happen in the order that the C/AL code is running. With Bulk Insert, the NAV-client delays the actual inserts until the last possible moment in the transaction. This means that tables get locked later, so it reduces the amount of time that a table is locked.

    "The last possible moment", means that the inserts will take place just before COMMIT. Or, if you use the return value of the INSERT command (IF Rec.INSERT THEN;). NAV also has to do the inserts if you run any MODIFY, DELETE or FIND methods on the table. So to take full advantage of this feature, you have to consider this when designing a NAV process.

    Note: This feature has nothing to do with the Transact SQL command "BULK INSERT". The Bulk Insert all happens on the client. It is not using any special SQL Server features.

    The Pseudo-code below illustrates how you will see the effect of Bulk Insert in a SQL Profiler trace. Without Bulk Insert, the SQL updates may look like this:

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

     

    With Bulk Insert, the same C/AL code would look lilke this:

     

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

    SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

    INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

     

    So, you will see exactly the same SQL updates. But with Bulk Insert, the inserts will be accumulated and run at the end of the process.

     

    Lars Lohndorf-Larsen (Lohndorf),

    Escalation Engineer

  • Microsoft Dynamics NAV Team Blog

    SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1

    • 0 Comments

    One of the major changes in Microsoft Dynamics NAV version 5 SP1 (in relation to performance on SQL Server), is a new way to send queries to SQL Server. In previous versions of NAV, we some times saw SQL Server 2005 optimizing query plans for extreme parameter-values, which - when re-used from cache for queries with other parameter-values - could cause long response time. A behaviour which is described in more details in KB 935395 on PartnerSource (login required). Some of the updates for NAV version 4 introduced new features to give better control of the query plans that SQL Server makes, such as index hints and Recompile-option.

    SP1 for NAV version 5 has restructured the way that queries are sent to SQL Server, with the aim that SQL Server will now make query plans that are optimized for average parameter-values rather than extreme parameter-values. It is also a method which lets SQL Server make the plan, without forcing it in a certain direction with index hints or recompile-option.

    Before NAV SP1, a typical query could look like this:

    declare @p1 int

    set @p1=180150033

    declare @p3 int

    set @p3=2

    declare @p4 int

    set @p4=1

    declare @p5 int

    set @p5=0

    exec sp_cursoropen @p1 output,N'SELECT * FROM "Demo Database NAV (5-0)"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">=@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_" ',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int','1105','','1753-01-01 00:00:00:000',0

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

    Notice the parameter-values in bold ('1105','','1753-01-01 00:00:00:000'). SQL Server will make a query plan based on running the query with these parameter-values. It will then cache this plan, and use the same plan for other queries which are identical, but have different parameter-values.

    In SP1, the query above will look like this:

    declare @p1 int

    set @p1=1073741861

    declare @p5 int

    set @p5=12290

    declare @p6 int

    set @p6=8193

    exec sp_cursorprepare @p1 output,N'@P1 varchar(20),@P2 varchar(20)',N'SELECT * FROM "W1500SP1RTM"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_">@P2 ORDER BY "G_L Account No_","Posting Date","Entry No_" ',1,@p5 output,@p6 output

    select @p1, @p5, @p6

    And then another query:

    declare @p2 int

    set @p2=180150031

    declare @p3 int

    set @p3=2

    declare @p4 int

    set @p4=1

    declare @p5 int

    set @p5=2

    exec sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''

    select @p2, @p3, @p4, @p5

    So before, we had one query. In SP1 we have two! So what's the benefit of that?

    If you look at the first query from SP1, notice that it is a sp_cursorprepare statement, and not sp_cursoropen. So the actual query is not run at this point. More importantly, the first query does not contain the parameter-values. This is the query for which SQL Server makes the query plan. Not having the parameter-values, SQL Server makes the plan based on its statistics about the data in the table. Only after this, NAV then executes the statement in the second query (sp_cursorexecute).

    This method guarantees that SQL Server's query plan will not be affected by the parameter-values. It means that some times, SQL Server is prevented from making the optimum query plan for a certain set of parameter-values. But remember that the query plan will be re-used for other parameter-values. So at the expense of having a few highly optimized queries, the method will give well optimized queries with better consistensy.

    Another cost of this method, is of course that now NAV sends 2 queries instead of 1, requiring an extra roundtrip to SQL Server. But this only happens the first time the query is run. If the same query is run again, NAV will only run the second query (sp_cursorexecute).

    One side effect of this is, that tracing a query in SQL Profiler becomes different. With SP1 you will see a lot of queries like the second one above, which does not show what NAV is actually doing. Take a look at the second query again:

    exec sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''

    With SP1 you will see a lot of queries like this, and then wonder what the actual query is. To find out, you need to use the cursor ID, and then find the original sp_cursorprepare statement, which will contain this line:

    set @p1=1073741861

    and then the actual query.

    In summary, this method is designed to give persistently, good overall performance, and to avoid sudden drops in performance that could be the result of cached query plans on SQL Server.

    Lars Lohndorf-Larsen (Lohndorf)
    Escalation Engineer

     

  • Microsoft Dynamics NAV Team Blog

    My experiences with Microsoft Dynamics NAV 5.0 SP1

    • 0 Comments

    I spent last week performing a Microsoft Dynamics NAV 5.0 Update 1 to Microsoft Dynamics NAV 5.0 SP1 database conversion for a customer.  The process went very smoothly and the customer was very excited about the increase in performance we were able to achieve. 

    Just as an illustration of our success, the customer ships hundreds of packages a day.  This process has been very painful in the past.  When the conversion was completed and a little tuning was done, we were able to achieve the following improvements …

    ·         Create a Shipment --> Reduced by 27%

    ·         Create a Pick --> Reduced by 15%

    ·         Register a Pick --> Reduced by 54%

    ·         Post a Shipment --> Reduced by 53%

     

    Now, I mentioned a little tuning was necessary so let me elaborate on that.  SP1 seems to highlight poor filtering used for the FlowFields.  The tuning that I had to do amounted to determining which CALCSUMS were not performing well.  I did this by using the Client Monitor in the Performance Toolkit.  I was then able to examine the filters that were applied and determine what a more appropriate key would be.  I added the additional key to the table along with the appropriate SumIndexField(s).   This made all the difference for this customer.

     

    This customer was also plagued by a large number of locking/blocking issues.  After applying SP1, with the Bulk Inserts and Indexed Views, the locking/blocking issues became almost non-existent.

     

    Some other pieces of information that might be helpful:

    ·         Log file size – during the conversion, the log file did NOT grow at all.

    ·         Database Size – when the conversion was done, this particular customer was able to get back approximately 10 GB of free space in the database out of a 65 GB database.

    ·         Conversion Time – it took between 40 -60 seconds per GB depending on what hardware was used.

    ·         Indexed Views – do NOT add any additional indexes to the Indexed Views.  When you design the table that the Indexed View is associated with, the view will be dropped and recreated and so will any additional indexes that you might have added.

     

    Robert Miller (rmiller)

    Escalation Engineer

Page 1 of 1 (11 items)