Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    New finsql.exe Command Prompt Options

    • 11 Comments

    Based on partner feedback and the increasingly important goal of automating processes around a Microsoft Dynamics NAV installation, we’re pleased announce that we have added company-related and database-related command-line options to finsql.exe, in addition to the existing options. Introduced in hotfix 2791548, you can use the following commands to perform development tasks without using the Microsoft Dynamics NAV Development Environment.

    • Create company - new
    • Delete company - new
    • Rename company - new
    • Create database - new
    • Compile objects
    • Export objects
    • Import objects

    Note

    To use the new and changed command-line options, you must install hotfix 2791548 (requires access to PartnerSource/CustomerSource).

    You can start the Microsoft Dynamics NAV Development Environment by running finsql.exe at the command prompt. By default, finsql.exe is located at C:\Program Files (x86)\Microsoft Dynamics NAV\70\RoleTailored Client\. The extended full syntax is:

    Finsql.exe [command=<command> | designobject=<object type> <object ID>,] [servername=<server>,] [database=<database>,] [collationname=<collation>,] [company=<company>,] [newcompany = <new company>,] [filter=<filter>], [logfile=<logfile>,] [username=<user name>,] [password=<password>,] [ntauthentication=<yes|no|0|1>]

    The full documentation is available on the MSDN Library. Additional documentation is available at Development Environment Commands.

    Parameters

    The following list describes the parameters that you can use with finsql.exe.

    collation

    The collation to use when you create a new database. The value must be one of the following:

      • A full language culture name. For example, da-DK for Danish or hu-HU for Hungarian.
      • A SQL Server collation name without case or accent. For example, Latin1_General_100.
      • A SQL Server collation name with case and accent. For example, Danish_Greenlandic_100_CS_AI.

    Use this optional parameter when you create a database with the command=createdatabase parameter.

    command

    The development command that you want to run. The possible values include:

      • CompileObjects
      • CreateCompany
      • CreateDatabase
      • DeleteCompany
      • ExportObjects
      • ImportObjects
      • RenameCompany

    If you specify this parameter and run a command, then the development environment does not open.

    For more information, see Development Environment Commands.

    company

    The name of the company that you want to create, delete, or rename.

    A company name cannot be more than 30 characters.

    If you create, delete, or rename a company with the command parameter, then the company parameter is required.

    database

    If you use the command=CreateDatabase parameter, then the database parameter specifies the name of the new database that you want to create. In this case, the database parameter is required.

    For all other commands, the database parameter specifies the database that you want to open.

    If you do not specify both the servername and the database parameter, then the database server and database that are stored in the .zup file are used. If you do not specify the database parameter but you do specify the servername parameter, then the Open Databasewindow opens so that you can specify the database name.

    For more information, see Saving Setup Parameters in the Zup File.

    designobject

    The type and ID of the object that you want to design. The possible values of the object type include:

      • Table
      • Page
      • Report
      • Codeunit
      • Query
      • XMLport

    The possible values of the object ID are 0 and any ID of an existing object. If you specify 0 for the object ID, then you open a new object to design.

    For more information, see DesignObject.

    file

    The path and file name. Use the file parameter for the following:

      • To specify the path and file name to which you export objects if you use the command=ExportObjects parameter. The file must have a .fob or .txt file name extension.
      • To specify the path and file name from which you import objects if you use the command=ImportObjects parameter. The file must have a .fob or .txt file name extension.
      • To specify the directory for the new database file if you use the command=createdatabase parameter.

    For more information, see ExportObjects and ImportObjects.

    filter

    A filter on the Object table.

    Use this parameter if you use the command parameter to compile, export, or import objects.

    For more information, see Development Environment Commands.

    id

    Specifies the ID of the .zup file. By default, the setup parameters are stored in and retrieved from the fin.zup file. If you specify the id parameter, then the setup parameters are stored in and retrieved from a file that is named <id>.zup.

    By default, the fin.zup file is located at C:\users\<user name>\AppData\Roaming\. To change the location of the .zup file, specify the path and ID in the id parameter.

    For more information, see Saving Setup Parameters in the Zup File.

    logfile

    Specifies the path and file name for the file that contains error messages that result from running finsql.exe with the command parameter. If there are no errors, then a log file is not created.

    For more information, see Development Environment Commands.

    newcompany

    The new name of the company that you rename.

    A company name cannot be more than 30 characters.

    If you rename a company with the command parameter, then the newcompany parameter is required.

    ntauthentication

    Specifies if you want to use NT authentication. The possible values are yes, no, 1, or 0. If you specify the username and password parameters, then you must specify ntauthentication=no or ntauthentication=0.

    objectcache

    Specifies the size of the object cache, in kilobytes. Objects such as code, descriptions, and windows that are used on the computer that is running the development environment are stored in the object cache.

    The default value is 32000.

    password

    Specifies the password to use with the username parameter to authenticate to the database. If you do not specify a user name and password, then the command uses the Windows user name and password of the current user to authenticate to the database.

    servername

    Specifies the name of the database server.

    If you do not specify both the servername parameter and the database parameter, then the database server and database that are stored in the .zup file are used. If you do not specify the servername parameter but you do specify the database parameter, then the Open Database window opens so that you can specify the database server name.

    For more information, see Saving Setup Parameters in the Zup File.

    temppath

    Specifies the path of the location to store temporary files that are created while Microsoft Dynamics NAV runs. These files are automatically deleted when Microsoft Dynamics NAV is closed.

    By default, these files are put in the Temp folder for the user, such as C:\Users\user name\AppData\Local\Temp\

    username

    The user name to use to authenticate to the database. The user name must exist in the database. If you do not specify a user name and password, then the command uses the Windows user name and password of the current user to authenticate to the database.

    Warning

    If User Access Control (UAC) is turned on and you do not specify to run the Command Prompt window as Administrator, then the Command Prompt window runs as a standard user. In this case, if you do not specify the username parameter and the current Windows user is an Administrator, then the command is run as the standard user.

    If you specify the username parameter, then you must also specify the password parameter and the ntauthentication parameter must be no or 0.

    For more information about database users and permissions, see Setting Database Owner and Security Administration Permissions in the MSDN Library.

     

     

    Best regards from the NAV Management Tools team

     

     

     

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2013 Compatiblity with Microsoft Windows 8 and Microsoft Windows Server 2012

    • 11 Comments

    We are proud to announce that Microsoft Dynamics NAV 2013 is compatible with Microsoft Windows 8 and Microsoft Windows Server 2012.

    We support the following versions of Microsoft Dynamics NAV with Microsoft Windows Server 2012 and Microsoft Windows 8:

    • Microsoft Dynamics NAV 2009 R2
    • Microsoft Dynamics NAV 2013

    For more information about requirements, see System Requirements for Microsoft Dynamics NAV 2013.

  • Microsoft Dynamics NAV Team Blog

    Application Test Toolset for Microsoft Dynamics NAV 2013

    • 11 Comments

    We recently shipped the Application Test Toolset for Microsoft Dynamics NAV 2013.

    The supplement is applicable to the following country releases of Microsoft Dynamics NAV 2013:

    W1, AU, CA, DE, DK, ES, FR, GB, IN, IT, MX, NL, NZ, SE and US

    The supplement contains the following per country:

    • Tools for managing and executing tests, capturing code coverage information, and selecting relevant tests out of available tests.
    • Between 7,000 – 9,000 real regression tests that we run, not dumbed-down sample tests.
    • Helper libraries for improving test development through reusing common functionality.
    • Searchable documentation of helper libraries with examples of how to use library functionality.

    You may attempt to apply the W1 version to other country versions, but you should expect parts of the toolset to not work.

    Installation

    To install the supplement, do the following:

    1. Download the supplement from PartnerSource here.
    2. Extract the content.
    3. Import the FOB file found under your corresponding country version folder. For example: .\AppTestToolsetNAV2013\DE\AppTestToolsetNAV2013-DE.fob
    4. After experimenting with the toolset, don’t forget to fill out the survey form and send it to Microsoft. We really appreciate your feedback and we rely on it to improve future versions of the toolset.

    How Do I Use This?

    The simplest way to make use of this supplement is to run the Test Tool page (130021) directly from the development environment. This launches the following page:

    Click Get Test Codeunits and then select All Test Codeunits.

    After Microsoft Dynamics NAV finishes loading all test codeunits, they are displayed on the Test Tool page. To run them, click the Run action and then select All.

    It will take about 1 – 2 hours depending on your machine and the setup to run all tests. When the run is completed it will show the results in the Test Tool page:

    Any changes done to the database through running of tests from the Test Tool are automatically rolled back using the Test Isolation testability feature of Microsoft Dynamics NAV 2013. (See the Additional Resources section in this post.)

    During typical development, it is unacceptable to have to wait hours to get results from tests, which is why we have built an advanced test selection feature to help identify the relevant tests. (See the Test Selection section in this post.)

    Alternatively, you can run individual tests or codeunits by selecting them and choosing either Active Line or Active Codeunit after you click the Run action.

    If any test fails, you can attach a debugger session and re-run the failing test. The debugger will then break at the line where the test failed and you will be able to inspect the call stack and examine variables to determine the underlying cause of the failure.

    Extending the Toolset With Your Own Tests

    After you have written your first test codeunit, you can easily integrate it into the tools we provide in this supplement.

    To include your own tests, in the Test Tool page, simply run the page from the development environment and click the action Get Test Codeunits and choose Select Test Codeunits. This will display a page listing all available test codeunits, including your own:

    Select the codeunits you would like to add to the tool and press OK. The new test codeunits appear at the bottom of the Test Tool list, and you can now select them and run them just like any of the tests we included.

    Again, Test Isolation prevents your tests from persisting changes to the database. During development it may be beneficial to actually see the output produced by the tests. It is possible to disable Test Isolation just by running the test codeunit directly from the development environment, however, instead we recommend attaching a debugger session, breaking at the test entry point, then stepping through test execution and inspecting variables to determine if your test is behaving as expected.

    Speeding Up Development of Your Own Tests

    The tests that we have developed are built on top of a layer of libraries that contain helper functionality to automate many aspects of Microsoft Dynamics NAV. For example, the library named Library – Sales contains functionality related to working with customers and sales documents, including creating new customers, sales headers, sales lines and posting sales documents. The library is extensive and has functionality in many areas of the product, such as finance, service, jobs, warehousing, inventory, etc.

    Instead of re-inventing the wheel when developing your own tests, we highly suggest that you look into our existing helper functionality for functions you can leverage.

    To help you find your way around the libraries, we have shipped a Microsoft Compiled HTML Help file (*.chm), which is bundled together with the .fob file you installed. When you open the .chm file, you are prompted with the following window:

    This lists all our libraries and the functions inside them. However, normally you don’t know which library to look for, You can search it from the Search tab. Try searching for "finance charge memo" and you will have a couple of choices to pick from:

    Code Coverage Tools

    Code coverage is the means of being able to track which part of the application code has been exercised during some activity. In Microsoft Dynamics NAV, code coverage is recorded by AL code line and in addition to knowing if a code line was exercised it also records the number of times it was recorded.

    The code coverage activity that we record can be any interaction with Microsoft Dynamics NAV, be it manual user interaction, automated test execution, NAS, Web services, etc. You can, of course, record code coverage of your own tests exercising your own objects.

    The toolset includes a page (130002), Code Coverage List, which you can use to track code coverage. Run the page from the development environment:

    From this page you can start/refresh/stop the code coverage recorder. If you click the Start action, the code coverage engine is turned on and code coverage is captured. However, you will not be able to see any updated information before you click either Refresh or Stop, at which time you are presented with the code coverage information:

    The information contains coverage of objects, triggers/functions and individual lines (code and empty) as determined by the column Line Type. Only lines of type Code can have coverage. Lines of type Trigger/Function show the average coverage of all code lines in the trigger/function. Lines of type Object show the average coverage of all code lines inside the object.

    From the above picture, you can read that the activity exercised 33.93% of the Currency table (4). It covered 100% of the OnModify trigger and that comes from 100% of a single Code line.

    It is often desirable to filter on Line Type = Object to first get a high-level overview of the coverage result:

    Then from here, you can filter to look at individual objects and expand the Line Type filter to include triggers/functions as well:

    This way you can drill-down into the results starting from a high-level view going to a low-level view.

    Note #1: Code coverage is recorded globally for all sessions when using this tool, so make sure you are running in a controlled environment so you don’t have any activity from unaccounted sessions.

    Note #2: Only objects that are touched by the activity are recorded, meaning the coverage of any object not in the list is implied to be zero. If you would like to force the recorder to include specific objects even if they are not covered, you can use
    the Load objects action and select the relevant objects from the subsequent page. This forces the code coverage engine to load these objects and provide information on even when no lines are covered.

    Test Selection

    Now that we have all the building blocks in place, I’d like to talk about an advanced feature we included with the tooling.

    As mentioned previously, having to wait hours to run all tests is not feasible from a development point of view. Therefore we shipped the Test Selection, which helps you narrow the set of tests down to the relevant tests.

    The feature works by analyzing the code coverage data from individual test codeunits and comparing it to the set of objects that have the Modified field set to Yes in the database.

    To use this feature, you run the Test Tool page and go to the Actions tab and click Import/Export Test Map action. On the request page, make sure the direction is Import and click OK. Browse to the bundled "AppTestToolsetNAV2013-<country
    code>-Map.txt" file and import the file. This will take a couple of seconds. After it is done, click the Get Test Codeunits action. The prompt will now include a third option:

    Select this third option and the tool will automatically detect the relevant tests to run and add them to your current suite.

    Note #1: In typical circumstances you would want to make sure your suite is empty before using this feature.

    Note #2: There is a small risk this feature will not identify all relevant tests in unusual circumstances. Thus we strongly recommend running the full regression suite before shipping anything to the customer.

    This feature also integrates with you own tests. Once enabled (by loading the Test Map), the information will auto-update when any test is run – including your own tests. This means that you can load the map, run your tests and now export the map to another text file. You can then load the new map into another database and the test selection feature will now be able to suggest your own tests based on modified objects in this other database. If your test codeunit is not present in the database, you will be prompted with a list of missing test codeunits that could not be added. Import the missing test codeunits into the database and re-run the test selection feature.

    Additional Resources

    -Simon Ejsing

  • Microsoft Dynamics NAV Team Blog

    Coming Soon: Enabling Commerce Gateway for Microsoft Dynamics NAV 2013

    • 10 Comments

    With Commerce Gateway for Microsoft Dynamics NAV, companies can use Microsoft Dynamics NAV to electronically exchange trading documents with business partners regardless of conversion requirements and data formats. This helps streamline business processes and reduce transaction costs. Commerce Gateway also makes it easier for companies to meet the changing demands of their trading partners, regardless of the industry they are in, the system that they use, or the standards that their partners require.

    As some of you may have noticed, Microsoft Dynamics NAV 2013 shipped without Commerce Gateway. We have explored a number of different options for enabling Commerce Gateway. This blog post describes the recommended upgrade path for users of Commerce Gateway to make their solution compatible with Microsoft Dynamics NAV 2013.

    We are currently working on a sample application that will demonstrate how to upgrade the Commerce Gateway solution. The sample will show how you should set up the connection between Microsoft BizTalk and Microsoft Dynamics NAV using a web service. The sample will be shipped through the hotfix process, after it has been through testing. For now, we expect to release the sample at the end of March.

    New customers that need Commerce Gateway will have to upgrade their solutions from Microsoft Dynamics NAV 2009 to Microsoft Dynamics NAV 2013. For more information, see Upgrading to Microsoft Dynamics NAV 2013.

    Commerce Gateway is still included in the license as part of the product and can be purchased as normal.

    Architectural changes

    In architectural terms, the Commerce Gateway solution consists of an application part, a communication part, and a BizTalk part. The communication part is the part that requires the most changes to be able to run with Microsoft Dynamics NAV 2013. We recommend that you change the communication part to use a web service connection instead of using the Request Client and the Request Server. For this to work, it is also required that you make a few changes to the Microsoft BizTalk configuration. There are minor changes to the application part.

    The main reason changes to Commerce Gateway were required for it to work on Microsoft Dynamics NAV 2013 are the architectural changes that were introduced with this version, such as the 64-bit Microsoft Dynamics NAV Server, as well as the removal of COM support on the server. Both changes require a change in the Commerce Gateway implementation, since it relies on a 32-bit COM implementation for the Request Server and the Request Client.

    Summary of recommended changes

    1. Upgrade application code to Microsoft Dynamics NAV 2013. The application code is impacted by the removal of support for forms.
    2. Change the connection between the Microsoft BizTalk server and Microsoft Dynamics NAV 2013 to use a new web service, which will be defined in the sample. Changing your solution to use web services will simplify the Microsoft Dynamics NAV 2013 installation, as you will no longer be required to use the Commerce Gateway Request server and the Commerce Gateway Request Client.
    3. The Microsoft Dynamics NAV 2013 architecture does not allow the use of automation on the server. Consequently, we have changed five objects to use the .NET Framework XML Document Object Model (DOM). You will have to upgrade your solution to merge in these changes.
    4. Change the connection setup on the Microsoft BizTalk Server to use the web service. You will need to create a new Send port and to reconfigure the orchestrations.

    All of the above changes will be described and implemented in the code sample that will ship at the end of March.

     

    Rikke Lassen

    Senior Program Manager

  • Microsoft Dynamics NAV Team Blog

    Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

    • 10 Comments

    In one of our previous blog post we discussed the possibility to do Transfooter and Transheader functionality in RDLC(SSRS) reports and describes a viable solution for this in RDLC.

    In this blog post we would like to suggest an alternative, a bit more economical and easier to implement solution for the same problem.

    For the demo we use the same table and the same report and will strive to achieve the same results as in the mentioned in our previous blog post.

    1. Create new report blank report with table 18

    clip_image002

    2. Create DataItem ”Customer”

    3. Go to Section Designer and add the following fields:

    • No.
    • Name
    • Debit Amount

    4. Save the report as ID 50000 – Transfooter / Transheader

    5. Now go to Visual Studio (View / Layout)

    6. Create table and add the fields No, Name and Debit Amount

    7. Give this table the name "MainTable"

    8. Now we have added the basic for this report. But I would also like to have a Grand total of the Debit Amount so I add this as well. I add this in the Footer of the table

    ="GrandTotal: " & sum(Fields!Customer__Debit_Amount_.Value)

    image

    9. Now if my report is printed I get a list of my all my customer with Debit Amount displayed and with GrandTotal in the end of the report:

    10. Now I create a small block of VBS code in order to perform some calculations and store intermediate data

    Open “Report->Report Properties” dialog and select “Code” tab, enter the following VBS code:

    11. Define a hashtable for storing running accumulated sums for each page of the report

    Shared RunningTotals As New System.Collections.Hashtable

    12. Define two public functions, which populate and query the hashtable from above

    Public Function GetRunningTotal(ByVal CurrentPageNumber)

    Return IIF(CurrentPageNumber > 0, RunningTotals(CurrentPageNumber), 0)

    End Function

     

    Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)

    RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber - 1)

    Return RunningTotals(CurrentPageNumber)

    End Function 

    image

    13. Ok, it’s now time to add a Transfooter and Transheader.

    Enable Page Header and Page Footer in the report (click “Report->Page Header” and “Report->Page Footer”).

    14. In the Page Footer I place a text box with the following expression:

    ="Transfooter subtotal = " & Code.SetRunningTotal( Sum(ReportItems!Customer__Debit_Amount_.Value), Globals!PageNumber)

    image

    This code actually performs the following actions:

    - calculate the sum of all “Debit Amount” values on the current page (sic)

    - adds this value to the running total, which has been already calculated for the previous page

    - returns this value as the actual running total for the current page

    15. In the Page header I place a text box with the following expression:

    ="Transheader subtotal = " & Code.GetRunningTotal(Globals!PageNumber-1)

    This code fetches the running total, calculated up to the previous page

    image

    16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report

    image

    17. Now I’m almost done but I would like to not see the Transheader on the first page and not to see the Transfooter on the last page.

    So I set the following expressions for the “Visibilty->Hidden” properties of the page header:

    =IIF(Globals!PageNumber > 1, False, True)

    And for the page footer:

    =IIF(Globals!PageNumber < Globals!TotalPages, False, True)

    18. Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print

    Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print:

    clip_image027

    clip_image029

    Question: Would this also work in the example of having a list of sales order lines per sales header and the sales order lines goes to multiple pages?

    Answer: The report above is a bit simplified in order to illustrate the point. It can be easily extended to support your scenario. I.e. the key for the hash should include page number AND header no to accomplish this.

    You can download the report object here, thanks to Nickolay Belofastow.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    It IS possible to instantiate the Visual Studio bridge.

    • 10 Comments

    Solution developers will often have different versions of Dynamics NAV installed on a single machine for their development environment. The way this is often implemented is to fully install the latest version and simply copy the Classic Client binary files for earlier versions into separate folders so you have something like this:

    NAV 2009 R2 fully installed with an R2 SQL Server database
    C:\NAVCLIENTS\NAV 40 SP3\ClassicClient (containing NAV binary files. A NAV 4.0 SP3 database is also accessible on the machine)
    C:\NAVCLIENTS\NAV 50 SP1\ClassicClient (similar setup to above)
    C:\NAVCLIENTS\NAV 2009\ClassicClient
    C:\NAVCLIENTS\NAV 2009 SP1\ClassicClient

    This approach has been working for solution developers for some time now. However, after installing Dynamics NAV 2009 R2, if you then try to view the Layout for an NAV 2009 SP1 Report you will get the following error:

    ---------------------------
    Microsoft Dynamics NAV Classic
    ---------------------------
    It is not possible to instantiate  the Visual Studio bridge.
    ---------------------------
    OK  
    ---------------------------

    This is due to a design change in NAV 2009 R2 which uses the .NetBridge rather than the old Visual Studio bridge component. However, the Visual Studio bridge files are included with the binaries in the NAV 2009 SP1 Classic Client folder so you can avoid the above error by running REGASM.EXE to register the DLL. The REGASM.EXE is included in the .Net Framework folder so executing the following command from within the NAV 2009 SP1 Classic Client folder should do the trick:

    C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm.exe Microsoft.Dynamics.Nav.VisualStudioBridge.DLL

    Of course, the REGASM.EXE may be located in a different path in your machine so alter the above command line as required.

    Gerard Conroy
    Microsoft Dynamics NAV Support

  • Microsoft Dynamics NAV Team Blog

    Running classic reports in RTC (RUNMODAL and a few other things)

    • 10 Comments

    Just a few points about running classic reports from RTC, based on common support scenarios:


    RUNMODAL (Not possible)

    RTC runs classic reports (reports with no RDL Layout) by starting the report engine from a classic client. However, RTC is not able to run classic reports MODALLY. RUNMODAL as opposed to just RUN means that execution of the  next line of C/AL code waits until the report has completed. Of course RTC reports which DO have a layout can run MODALLY without any problems.

    So Report.RUNMODAL on a classic report from RTC does exactly the same as Report.RUN, i.e. RTC will launch the classic report engine but not wait for the report to finish before executing the next line of code. In most cases this doesn't matter. But the challenge is when the next line of code dependeds on the report having completed, for example to email the report, convert it to pdf, write back a result of the report to the database, or something else.

     

    Running batches of classic reports (Improvement)

    The good news is that there has been a few small other improvements relating to running classic reports from RTC. When the classic client closes, it updates a few files like the .zup file, and in the case of breakpoints having been used, also NaviBP.xml as described here:
    http://msdn.microsoft.com/en-us/library/dd355196.aspx
    In case we launched a batch of classic reports, one instance of the classic report engine would start up for each report. And each instance could overwrite each other's files, leading to error messages like this:

    You cannot use the file C:\Users\[USER]\AppData\Roaming\fin.zup  because it is already in use.

    and


    Microsoft Visual C++ Runtime Library
    Runtime Error!

    Program: C:...

     This application has requested the Runtime to terminate it in an unusual way.
    Please contact the application's support team for more information. 

    But with KB 2398170 (build 31587) the behaviour is changed, so that now when the classic client closes the report engine, it willl 1) only try to update the .zup file, and 2) not try to update naviBP.xml at all, avoiding the error messages mentioned above.

     
    Licenses (No, it doesn't consume a license)

    When you run a classic report from RTC, this does NOT consume a user license. If you watch the Session table while a classic report is running, you will see that a new session has connected, but with "Application Name" = '111'. In this way the system knows not to count it as a normal NAV client, so it will not count as a concurrent session.

     

     

    Lars Lohndorf-Larsen

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 9 for Microsoft Dynamics NAV 2013 R2 has been released

    • 10 Comments

    Cumulative update 9 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2. 

    The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU - Australia
    • AT - Austria
    • BE - Belgium
    • CH – Switzerland
    • CZ Czech Republic (New)
    • DE - Germany
    • DK - Denmark
    • ES - Spain
    • FI  - Finland
    • FR - France
    • IS - Iceland
    • IT - Italy
    • NA - North America
    • NL - Netherlands
    • NO - Norway
    • NZ - New Zealand
    • RU – Russia
    • SE - Sweden
    • UK - United Kingdom

    This cumulative update also introduces new application merge utilities: A new set of Windows PowerShell cmdlets that can help you through code upgrade.

    You can use the new cmdlets to modify application object source files in the Microsoft Dynamics NAV 2013 R2 Development Shell, or by importing the Microsoft.Dynamics.NAV.Model.Tools.psd1 module into the Windows PowerShell Integration Script Environment (ISE). The new application merge utilities install when you choose the Developer option in Microsoft Dynamics NAV 2013 R2 Cumulative Update 9 Setup, or if you add the development environment to another installation option.

    For more information, see the MicrosoftDynamicsNAV2013R2CU9_MergeApplicationObjectSourceFiles.pdf whitepaper, which is attached to this blog post. We will also write about the new cmdlets here on the blog.

    Where to find cumulative update 9

    You can download cumulative update 9 from KB 2977473 – Cumulative Update 9 for Microsoft Dynamics NAV 2013 R2 (Build 37221).

    For a full list of all hotfixes included in cumulative updates for Microsoft Dynamics NAV 2013 R2, see the following CustomerSource and PartnerSource pages: 

    CustomerSource:

    PartnerSource

    More Information

    For more information about cumulative updates for Microsoft Dynamics NAV 2013 R2, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2

    For a list of all release cumulative updates, see Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2.

  • Microsoft Dynamics NAV Team Blog

    Upgrading from Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2015

    • 10 Comments

    Quite a few people have asked that we excavate the guidance for how to upgrade from Microsoft Dynamics NAV 2009 R2 directly to Microsoft Dynamics NAV 2015. We provided this guidance in the blog post for Cumulative Update 1, but as time progresses, this becomes harder to remember and to find. So here you go:

    Introduction

    This blog post describes the tasks required for upgrading a Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2015.

    Using the upgrade toolkit and conversions tools that are available, the tasks will lead you through the process of converting the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2013, converting the Microsoft Dynamics NAV 2013 database to Microsoft Dynamics NAV 2015, and finally, converting the old data with the old table and field structure to function together with the table and field structure of Microsoft Dynamics NAV 2015.

    In order to use the toolkit to upgrade data, you will also need the Microsoft Dynamics NAV 2013 development environment and Microsoft Dynamics NAV 2015.

    Data Upgrade Short Overview

    The following lists provides a brief description of the steps involved in the data upgrade. The steps are detailed in the task sections that follow.

    1. In Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1, compile all tables.
    2. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    3. Import Microsoft Dynamics NAV 2015 license into the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database.
    4. Import Upgrade601800.[Country].1.fob (use Replace All action on the Import Worksheet).
    5. For each company in the database, run form 104001, Upgrade - Old Version, and choose the Transfer Data action.
    6. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    7. Run form 104001, Upgrade - Old Version, and then choose the Delete Objects action.
    8. Uninstall Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1.
    9. Change the compatibility level of the database by using SQL Server Management. Studio.
    10. Download the latest Cumulative Update of Microsoft Dynamics NAV 2013 and install the Microsoft Dynamics NAV 2013 development environment.
    11. Open the old database with Microsoft Dynamics NAV 2013 development environment and convert the database.
    12. Uninstall Microsoft Dynamics NAV 2013.
    13. Make a full SQL backup of the database.
    14. Install Microsoft Dynamics NAV 2015 including the Development Environment, Microsoft Dynamics NAV Server, and Administration Tool components.
    15. Open the old database with Microsoft Dynamics NAV 2015 Development environment and convert the database.
    16. Make sure the system tables (which have IDs in 2000000004…2000000130 range) are compiled (On the Compile window, set the Synchronize Schema option to Later).
    17. Set database role membership for the service account that is used by the Microsoft Dynamics NAV Server instance to db_owner.
    18. Connect Microsoft Dynamics NAV Server instance to the old database.
    19. Start the development environment as administrator.
    20. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    21. Import all new Microsoft Dynamics NAV 2015 objects (use the Replace All action on the Import Worksheet, and then set the Synchronize Schema option to Later).
    22. Import Upgrade601800.[Country].fob.
    23. Compile the tables which are not already compiled (set Synchronize Schema option to Later).
    24. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    25. Make a full SQL backup of the database
    26. Run the data upgrade process from the Tools menu by selecting Data Upgrade, and then Start.
    27. Delete obsolete tables and upgrade toolkit objects.
    28. Import updated permissions sets and permissions.
    29. Set language of the database.
    30. Import or register the control add-ins.

    Task 1: Run Upgrade Step 1 in the Microsoft Dynamics NAV 2009 R2 or SP1 development environment (Classic client)

    1. Make sure that all table objects have compiled successfully.
      To compile all table objects, in Object Designer, select all objects of type Table, and then on the Tools menu, choose Compile. During compilation, Microsoft Dynamics NAV generates or regenerates the BLOB content in the Object Metadata table that is used in the later steps.
    2. Create a copy or a backup of the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database, and open it in the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 development environment.
    3. Add your Microsoft Dynamics NAV 2015 partner license to the database.
      You can do this by selecting the Save License in Database field in the Alter Database window. If the field was not already selected, a dialog box opens so you can specify the location of your partner license. If the field was already selected, upload the partner license in the License Information window.
    4. Open Object Designer, and then import Upgrade601800.[Country].1.fob from the \UpgradeToolKit\Local Objects folder. In the Import Worksheet window, choose Replace All, and then choose the OK button to import the objects.
    5. For each company in the database, do the following:
      1. Open the company, and make the relevant changes to data. For more information, see Task 3: Data/Object Changes Prior to Step 1 in the MSDN Library.
      2. In Object Designer, run form 104001, Upgrade - Old Version. Choose the Transfer Data button.

        Important: After you have completed this step for all companies, we strongly recommend that you create a full SQL backup the database by using SQL Server management tools.
    6. When you have transferred all data for all companies, in the Upgrade - Old Version window, choose the Delete Objects button.
      This action deletes all objects in the database that are not tables, but also obsolete tables that belong to functionality that is not available in Microsoft Dynamics NAV 2015.

    Task 2: Convert the Microsoft Dynamics NAV 2009 R2 or SP1 database to a Microsoft Dynamics NAV 2013 database

    1. Uninstall Microsoft Dynamics NAV 2009 R2.
    2. Download the latest available Cumulative Update for Microsoft Dynamics NAV 2013.
      The following is article lists the Cumulative Updates that have been released for Microsoft Dynamics NAV 2013: https://mbs2.microsoft.com/Knowledgebase/kbdisplay.aspx?scid=kb,en-us,2842257 
    3. Run the installation program (setup.exe) for Microsoft Dynamics NAV 2013, choose Choose an installation option, choose Custom, and then choose the Client option (with the Development Environment).
    4. Change the compatibility level of the database according to the following:
      For SQL Server 2008 R2 set the compatibility level of the database to 100.
      For SQL Server 2012, set the compatibility level of the database to 110.
      To do this, you use Microsoft SQL Server Management Studio as follows:
      1.  Start Microsoft SQL Server Management Studio and connect to the SQL Server instance that includes the Microsoft Dynamics NAV database.
      2. Right-click the Microsoft Dynamics NAV database, and then select Properties.
      3. In the Database Properties window, choose the Options page, and then set the Compatibility Level to the correct value.
    5. In the Microsoft Dynamics NAV 2013 development environment, open the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database and agree to convert the database.
      During this step, Microsoft Dynamics NAV converts all text and code fields to Unicode format by changing their SQL Server data type. This conversion requires more disk space than usual, since both the database and the log file will grow in size considerably. It can also be a lengthy process.
      If your Microsoft Dynamics NAV 2009 R2 database is using SQL Server Collation, within the same step the collation will be changed to a suitable Windows collation. This is because Microsoft Dynamics NAV 2013 and higher versions only support Windows Collation.

      Important: After the database conversion has completed, we strongly recommend that you create a full SQL backup of the database.
    6. The technical upgrade of the database to Microsoft Dynamics NAV 2013 is complete. You can now uninstall Microsoft Dynamics NAV 2013.

    Task 3: Convert the Old Database to a Microsoft Dynamics NAV 2015 Format

    1. Install Microsoft Dynamics NAV 2015.
      During the installation of Microsoft Dynamics NAV 2015, select the Choose an installation option, then select the Custom option, where you choose to install the Client (with the Development Environment), Server, and Administration Tool components.
    2. To convert the old database to a Microsoft Dynamics NAV 2015 format, open the old database in the Microsoft Dynamics NAV 2015 development environment, and then follow the instructions.
    3. After the database conversion is completed, verify that all system tables are compiled.

    System tables have IDs in the range 2000000004 to 2000000130. If any tables are not compiled, then you must compile them. When you compile tables, on the Compile dialog box, set the Synchronize Schema option to Later.

    Task 4: Connect a Microsoft Dynamics NAV 2015 Server Instance to the Converted Database

    1. Using Microsoft SQL Server Management studio, add the service account that is used by the Microsoft Dynamics NAV Server instance (for example, NT AUTHORITY\NETWORK SERVICE) as a member of the db_owner role in the Microsoft Dynamics NAV database on SQL Server.
      For more information, Giving the account necessary database privileges in SQL Server.
    2. Using the Microsoft Dynamics NAV Server Administration tool, connect a Microsoft Dynamics NAV Server instance to the converted database.

    For more information, see How to: Connect a Microsoft Dynamics NAV Server Instance to a Database.

    Task 5: Run Schema Synchronization to initialize the database.

    You can run the schema synchronization from the Microsoft Dynamics NAV Development Environment or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Sync. Schema For All Tables, and choose With Validation, and then follow the schema synchronization instructions.

    If synchronization errors occur, run the schema synchronization from Microsoft Dynamics NAV 2015 Administration Shell as described in the following procedure. This enables you to get the complete list of tables that could not be synchronized.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    1. Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator.
    2. Run Sync-NavTenant cmdlet as follows:

    Sync-NavTenant –ServerInstance <ServerInstanceName> -Mode Sync

     Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database. For more information, see How to: Run the Sync-NAVTenant Cmdlet to Synchronize the Tenant Database with the Application Database.

    Task 6: Import the Application Objects to the Converted Database

    1. In the development environment, import all the application objects that you want in the Microsoft Dynamics NAV 2015 database.
      When you import the FOB file that contains the Microsoft Dynamics NAV 2015 objects, on the Import Worksheet, choose Replace All, and then choose the OK button to import the objects.
      On the dialog box for selecting the schema synchronization, set the Synchronize Schema option to Later.
    2. After you have imported Microsoft Dynamics NAV 2015 objects, import the upgrade toolkit FOB file Upgrade601800.[Country].fob from \UpgradeToolKit\Local Objects folder.
    3. Compile all objects that have not been compiled yet. On the Compile dialog box, set the Synchronize Schema option to Later.

    Task 7: Run the Schema Synchronization

    Similar to task 5, run the schema synchronization either from the development environment (Tools – Sync. Schema for All Tables – With Validation…) or from Microsoft Dynamics NAV 2015 Administration Shell.

    This will synchronize the data schema changes of the newly imported tables to the SQL tables. It will also run the Upgrade Toolkit Step 1 logic if it is defined in the upgrade codeunit. This step will be performed for all companies in the database, so you don’t have to re-run it for other companies.

    Note: After the schema synchronization has successfully completed, we strongly recommend that you take a full SQL Server backup of the database. 

    Task 8: Run the Data Upgrade to perform Upgrade Toolkit Step 2

    A data upgrade runs the upgrade toolkit objects, such as upgrade codeunits and upgrade tables, to migrate business data from the old table structure to the new table structure. You can start the data upgrade from the Microsoft Dynamics NAV Development Environment or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Data Upgrade, and then choose Start and follow the instructions on the Start Data Upgrade window.

    If you want to see the result of running the Data Upgrade action, run the following cmdlet from Microsoft Dynamics NAV 2015 Administration Shell:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Detailed | Out-GridView

    In the output of this cmdlet, you can see which upgrade codeunits and functions were run, which companies have been upgraded, the time it took to run each upgrade function, and a detailed description of errors that occurred during the data upgrade.

    If errors occurred while running the data upgrade, you can resolve them directly in the database (for example by updating the upgrade codeunits) and then resume the data upgrade. To resume the data upgrade, on the Tools menu, choose Data Upgrade, and then Resume.

    Note: To optimize upgrade performance and effectively use the available computer resources, the data upgrade functions are executed in parallel and across all companies by default. While performing this step, if you discover that some functions are locking each other from executing, then you can configure the data upgrade to execute data upgrade functions in serial instead of parallel. To do this, on the Start Data Upgrade window, set the Execution Mode option to Serial. For more information see http://msdn.microsoft.com/en-us/library/dn762348(v=nav.80).aspx.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator, and then run Start-NavDataUpgrade cmdlet as follows:

    Start-NavDataUpgrade -ServerInstance <ServerInstanceName> -Force

    Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database.

    To view the progress of the data upgrade, you can run Get-NavDataUpgrade cmdlet as follows:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Progress

    Note: The data upgrade process runs CheckPreconditions and Upgrade functions in the upgrade codeunits. If any of the preconditions are not met or an upgrade function fails, you must correct the error and resume the data upgrade process.

    Task 9: Delete the Upgrade Objects and Obsolete Tables

    At this point, you have upgraded the database to Microsoft Dynamics NAV 2015. Now, you can delete the tables which are not needed into the new application, plus the upgrade codeunits and upgrade table objects that you imported in task 6.

    For application tables, you can see which the tables are marked for deletion in the Version List column in the Tables list of Object Designer. During the data upgrade that you performed in task 8, these tables were marked with the text Old Unused Table - marked for deletion. Upgrade objects are marked with the text Upgrade Toolkit Object - marked for deletion.

    When you delete tables, if you don’t need the data that is saved in the tables, then on the Delete dialog box, set the Synchronize Schema option to Force.

    Task 10: Import upgraded permission sets and permissions by using the Roles and Permissions XMLports

    You import the permission sets and permissions XML files according to the following procedure.

    To import the permission sets and permissions

    1. Delete all permission sets in the database except the SUPER permission set.
      In Object Designer, run page 9802 Permission Sets, and then delete the permission sets.
    2. Run XMLport 9171 Import/Export Permission Sets to import the permission sets XML file.
      In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permission sets XML file.
    3. Run XMLport 9172 Import/Export Permissions to import the permission XML file.
      In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permissions XML file.

    Task 11: Set the Language of the Customer Database

    In the development environment, choose Tools, choose Language, and then select the language of the original customer database.

    Task 12: Add New Control Add-ins

    The database is now fully upgraded and is ready for use. However, you may want to add the new client control add-ins that are included in Microsoft Dynamics NAV 2015. These are not added by the upgrade process. The following client control add-ins are available from the Microsoft Dynamics NAV product media:

    • Microsoft.Dynamics.NAV.MicrosoftDynamicsOnlineConnectControl
    • Microsoft.Dynamics.Nav.Client.BusinessChart
    • Microsoft.Dynamics.Nav.Client.PageReady
    • Microsoft.Dynamics.Nav.Client.PingPong
    • Microsoft.Dynamics.Nav.Client.VideoPlayer
    • Interactive Timeline Visualization Add-in

    You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client. For more information, see How to: Register a Windows Client Control Add-in.

  • Microsoft Dynamics NAV Team Blog

    Updated System Requirements for Microsoft Dynamics NAV 2015 Windows Client

    • 9 Comments

    The system requirements for Microsoft Dynamics NAV 2015 have been updated in the MSDN Library. In the original version, the section for the Windows client contain misleading information about the supported operating systems. This was a bug in the way that system requirements are gathered and tested, and we have changed the process accordingly. We are fully aware of the confusion that the misleading information about only supporting 64-bit editions has caused.

    The Microsoft Dynamics NAV 2015 Windows client supports both 32-bit and 64-bit editions of Windows 8.1, Windows 8, and Windows 7.

    As always, the latest version is in the MSDN Library at the following location: http://msdn.microsoft.com/en-us/library/dd301054(v=nav.80).aspx.

    Best regards,

    The NAV UA  team and the NAV Release Management team

  • Microsoft Dynamics NAV Team Blog

    Introducing the Microsoft Dynamics NAV Application Profiler

    • 9 Comments

    Did you ever wish you could monitor how your application code performs at real time? We have produced a sample and a video that can help you get started with C/AL tracing and application performance profiling.

    Get the Microsoft Dynamics NAV Application Profiler here, http://go.microsoft.com/fwlink/?LinkId=403898, and watch the video on how to install, configure and use this tool to enable C/AL code tracing and determine application performance during code execution here, http://go.microsoft.com/fwlink/?LinkId=403897.

     

    David Worthington and Dmytro Sitnik from the Dynamics NAV team

    Format: ???
    Duration: 7:00

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 7 for Microsoft Dynamics NAV 2013 R2 has been released

    • 9 Comments

    Cumulative update 7 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2. 

    The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU - Australia
    • AT - Austria
    • BE - Belgium
    • CH - Switzerland
    • DE - Germany
    • DK - Denmark
    • ES - Spain
    • FI  - Finland
    • FR - France
    • IS - Iceland
    • IT - Italy
    • NA - North America
    • NL - Netherlands
    • NO - Norway
    • NZ - New Zealand
    • RU – Russia
    • SE - Sweden
    • UK - United Kingdom

    Where to find cumulative update 7

    You can download cumulative update 7 from KB 2964528 – Cumulative Update 7 for Microsoft Dynamics NAV 2013 R2 (Build 36703 - our apologies for originally posting the wrong build number).

    For a full list of all hotfixes included in cumulative updates for Microsoft Dynamics NAV 2013 R2, see the following CustomerSource and PartnerSource pages: 

    CustomerSource:

    PartnerSource

    More Information

    For more information about cumulative updates for Microsoft Dynamics NAV 2013 R2, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV: Faster than ever.

    • 9 Comments

     

    First, let me start by saying that based on the evidence so far: It is. Significantly.

    A number of the changes to the Dynamics NAV 2013 architecture contribute to the performance boosts that many of the test have shown. To outline some of the changes:

    • Middle tier is 64 bit
    • We are no longer using cursors but MARS
    • Ability to auto-update sift fields when looping, rather than repeating the process for each call. SETAUTOCALCFIELDS reduces the amount of statements issued
    • Pages containing flowfields now issue ‘SmartSQL’ queries, basically one source table query with outer joins for each flowfield query, again reducing the ‘chattiness’
      and calculating sift values in one server roundtrip
    • Global cache
    • REPEATABLEREAD is default isolation level
    • Locking changes in posting routines, locking at later point and reducing overall blocking time

    … and more.

    However(!), a few things have surfaced in the course of time that are not as explicitely documented as the changes above, nor as apparent, and might have unexpected side effects. I have collected some of the side effects that you might or not be aware of and that might leave you panicking if not certain what you’re facing.

     

    • Pages (with FlowFields) will in general run faster on Dynamics NAV 2013 than on NAV 2009 due to SmartSQL queries and reduced chattiness. Flow Fields on a page (all of them) are calculated in one go, which greatly reduces number of statements issued. But SmartSQL queries are not cached. Also, since we changed to MARS, SQL seems to be more sensitive to an index's column cardinality then before. You might experience that (depending on various factors) some queries that have run fine in the past now take much longer, this is due to a poor execution plan on SQL. With Dynamic cursors, query plan optimizer tended to optimize for the ORDER BY, while with MARS, SQL is free to choose.  It does a good job at it too, but in rare occasions might chose poorly. These cases are exceptions and contrary to the popular belief, this is not caused by the SmartSQL queries. These queries will have same poor execution plan and perform poorly even when isolated from the SmartsSQL query.

     

    Consider the following example. This is just an illustration of the problem, constructed on CRONUS extended database:

    A lot of Inventory transactions are posted through the Item Journal, generating a lot of Post cost to the G/L Entry. After this, when browsing an item list and opening an Item card, the page opens very slowly.

    After locating the offending query in the SQL profiler and running it isolated in Microsoft SQL Server Management Studio with ‘Include Actual Execution Plan’ option enabled, the plan looks similar to the one shown below:

     

     

    Each sub-query shows reasonably (small) percentage of cost and no apparent reason for bad execution plan. There are no obvious extreme costs, however there is a Clustered Index Scan here:

     

    Looking at the filter that SQL Server applies, namely it filters on “Post Value Entry to G_L”. “Item_No_” = “Item”.”No_”:


     

     

    Although SQL Server reports Operator Cost as small, it shows CPU Cost in excess of 2.3 in approx. 1.94 executions. So, it is likely scanning the table twice and unfortunately the table has 4,7 million rows.

    Although it is not obvious from the actual SQL Server execution plan that this is a problem, profiling with the SQL Server Profiler reports the query to use more than 5 seconds of CPU, while doing 131.519 reads to fetch 52 rows:

     
     

    The reason the Duration is on par with CPU Seconds is that all reads are logical from SQL Server Buffers. Re-issuing the query after adding the supporting index shows this in SQL Server Profiler:

     


    So Reads were reduced by a factor of 100 and (warm) duration was reduced by a factor of 40.

    As you can see, these poor execution plans are not caused by the SmartSQL. However the fact that the SmartSQL queries don’t cache their results will only amplify the issue. To solve it, we have to tackle the performance of that one isolated query by creating a covering index to improve the execution plan.

    And no, it won’t help to merely customize the page or change the visibility of the field. As long as it is contained in page metadata (so unless removed from page altogether), it will be calculated.

    So in short, if you do run into an issue of rather dramatic slowness of a page containing flowfields in Dynamics NAV 2013 or higher, isolating and testing the Flow Field queries separately (focusing on ones with clustered index scan, regardless of cost) should lead you to the culprit fairly quickly.  A supporting index should resolve the problem.

    • Temp tables: These are now moved to .NET. When you create a Temp record, your private C# TempTableDataProvider object is created. It will store whatever records you insert into a C# Dictionary, with the Primary Key Fields as the key for the Dictionary. The Dictionary is in memory and stores records in the order they
      are inserted.  When you add a SetCurrentKey, an AVLTree is built on the fly at first FindXX() you perform with that (current) key. In terms of performance, this is an expensive operation. This tree is in memory however, so it will be cached and reused later. If you issue a query without calling SetCurrentKey, an AVLTree for the primary key will be used.

    However, maintaining all this can consume quite a lot of memory on your middle tier, so plan for ample memory when scaling. Also, as mentioned above, querying temp tables is cached but sorting them is a fairly expensive operation, just something to keep in mind.

    • The next one is not strictly a performance issue, but can have a fairly drastic performance side-effect if you’re affected by it, so deserves to be mentioned:

    You might (or might not) be aware that the transaction scope and behavior of a page action has changed in Microsoft Dynamics NAV 2013 and higher. This will be especially significant if you are calling functions in codeunits (or objects other than the source table), passing the REC as a parameter and intend to lock the record in that function.

    Consider the following example: You have added an action that invokes a function in a codeunit, that in turn locks/ modifies the record (typically calling custom posting routine)

    So OnAction trigger code is, for example, as follows:

     

      PostingFunction(Rec);

     

    Where PostingFunction is a function (in any object other than the source table).

    Now, the consequence of the previously mentioned transaction scope change and the fact that you’re locking the record in the function, is that the entire source table (Rec) you passed as a parameter in the example above, is locked. In other words, you’re passing the current record as a parameter, but you are locking the whole table. The behavior would cause more damage than good to change at this point due to all functionality it would affect, so it won’t be changed, but fortunately – if
    you’re aware of this issue, the solution is very simple:

    SETSELECTINOFILTER(Rec);  //adding this line

    PostingFunction(Rec);

    Adding the line above should reduce the scope of locking and leave you with locking just the one record (or selection of records if on a list). This applies to card pages as well.

     

    • Last, but not least: you might have noticed increased Async_Network_IO waits. These cause quite a lot of concern out there. However, this is a symptom and not a problem per se.

    When Dynamics NAV issues a query that returns thousands of rows, SQL Server will start a work thread to return the result. If the application does not consume the rows as fast as they are delivered from SQL Server then the SQL Server work thread will have to wait and that shows up as wait type Network_Async_IO. If the application never consumes the entire result set then the worker thread will be hanging until the transaction is ended (when we close the statement), or if it was a read transaction, for a longer period either until the connection is closed (as idle) or if the statement is overwritten by another statement (statement cache is full).

    Example: when we do a FINDSET, a number of records (50, 100...) is retrieved. If it is a larger set it will run until all records are retrieved (or buffer is full), even if only first 10 are actually read by application. Eventually the session goes to sleep and after transaction ends sleeping sessions are removed. So in short, these are merely reflecting NAV data access methods, and are not a problem as such. If you want to reduce these, make sure you’re reading all the rows you’re asking for when using FINDSET, otherwise use FIND(‘-‘) or FIND(‘+’).

     

    With thanks to Jesper Falkebo and Jens Klarskov Jensen

     

    Jasminka Thunes

    Microsoft CSS

     

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

  • Microsoft Dynamics NAV Team Blog

    MagicPath (DOWNLOADFROMSTREAM and UPLOADINTOSTREAM without a dialog box)

    • 9 Comments

    The C/AL commands DOWNLOADFROMSTREAM and UPLOADINTOSTREAM have the purpose of sending files between RTC and the NAV Server. A few times now, we had the question: How can we use these functions without it displaying the dialog box to select a file and folder name?

    This is how you can automatically download and upload files without any user interactions:

    The trick is to use MagicPath, like in codeunit 419 "3-Tier Automation Mgt.". MagicPath is initiated by setting the folder name to '<TEMP>' like this:
    DOWNLOADFROMSTREAM(IStream,'','<TEMP>', '',MagicPath);

     

    DOWNLOADFROMSTREAM

    The code example below will copy a specific file from the NAV Server to the RTC machine with no questions asked about folder or file name or anything else:


    IF NOT ISSERVICETIER THEN
      EXIT;
    FileToDownload := 'c:\Temp\ServerFile.txt';
    FileVar.OPEN(FileToDownload);
    FileVar.CREATEINSTREAM(IStream);
    DOWNLOADFROMSTREAM(IStream,'','<TEMP>', '',MagicPath);
    MESSAGE('Path = ' + MagicPath);

    FileVar.CLOSE;

     

    Variables:

    Name Data Type Length
    FileToDownload  Text 180
    FileVar File
    IStream  InStream
    MagicPath  Text 180

     


    Now we have the file on the RTC machine, and MagicPath tells us its location. The location will be something like this:
    C:\Users\[UserName]\AppData\Local\Temp\Microsoft Dynamics NAV\4612\__TEMP__ff7c5a286cfd463f9f7d92ae5b4757e2

    The number 4612 in the MagicPath comes from the Process ID of RTC.

     

    Handling files client side

    So, what if we wanted to rename it to a specific name? We have the FILE object in C/AL, but of course since C/AL runs on the NAV Server and not on RTC, this won't work since the purpose of the above is exactly to copy the file to the client machine. Instead, use this automation:

    'Microsoft Scripting Runtime'.FileSystemObject

    Then create an instance ClientSide:
    CREATE(FileSystemObject,TRUE,TRUE);


    So, if you wanted to continue the code above and place and name the file to something specific on the client's machine, add these lines:


    CREATE(FileSystemObject,TRUE,TRUE);
    DestinationFileName := 'c:\Temp\newfile.txt';
    IF FileSystemObject.FileExists(DestinationFileName) THEN
      FileSystemObject.DeleteFile(DestinationFileName,TRUE);
    FileSystemObject.CopyFile(MagicPath,DestinationFileName);
    FileSystemObject.DeleteFile(magicpath,TRUE);

     

     
    UPLOADINTOSTREAM


    MagicPath works both ways. But with DOWNLOADFROMSTREAM it creates MagicPath for you and tells you where it is. With UPLOADINTOSTREAM you need to know it in advance. Remember the MagicPath location above includes the Process ID of RTC. One way could be to work that out somehow. But what I would suggest instead, is to download a temp test file first, then see where MagicPath downloads it to. The path for upload will be the same:


    // download a temp file to get MagicPath
    FileVar.CREATETEMPFILE;
    FileVar.CREATEINSTREAM(IStream);
    DOWNLOADFROMSTREAM(IStream,'','<TEMP>', '',MagicPath);
    FileVar.CLOSE;
    MESSAGE(MagicPath);


    Then extract the folder name from MagicPath:

    FOR i := STRLEN(MagicPath) DOWNTO 1 DO BEGIN
      IF MagicPath[i] = '\' THEN BEGIN
        MagicPath := COPYSTR(MagicPath,1,i);
        i := 1;
      END;
    END;

    Once you know the location of MagicPath, the next step is to copy the file you want to upload into that folder:

    FileToUpload := 'newfile.txt';
    FolderName := 'c:\Temp\';

    IF ISCLEAR(FileSystemObject) THEN
      CREATE(FileSystemObject,TRUE,TRUE);
    FileSystemObject.CopyFile(FolderName + '\' + FileToUpload,MagicPath + '\' + FileToUpload);

    Then use UPLOADINTOSTREAM to upload the file from MagicPath to the NAV Server:
    UPLOADINTOSTREAM('','<TEMP>','',FileToUpload,IStream);


    And finally, save the InStream to a file on the server:

    FileVar.WRITEMODE(TRUE);
    FileVar.CREATE('c:\Temp\OnServer.txt');
    FileVar.CREATEOUTSTREAM(OStream);
    COPYSTREAM(ostream,istream);
    FileVar.CLOSE;

     

    So, put all this together and the end result is:

    The file c:\Temp\ServerFile.txt gets downloaded to C:\Temp\NewFile.txt, and then uploaded back to the server as C:\Temp\OnServer.txt.

     

     

    Lars Lohndorf-Larsen

    Dynamics NAV Support EMEA

     

     

     

     

     

  • Microsoft Dynamics NAV Team Blog

    How to create/read xml file from Microsoft Dynamics NAV without using xmlports

    • 9 Comments

    Periodically we receive support requests about XMLport errors, which really are missing features in xmlport's functionality.
    However most of these requests are rejected because: xmlports are not supposed to fulfill all possible xml standard scenarios.
    So there always will be xml file which can't be created or readed by NAV xmlport.
    Possible workaround could be to use XML DOM automations.

    Create it by codeunit:

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

     OBJECT Codeunit 50052 xml create
    {
      OBJECT-PROPERTIES
      {
        Date=03/24/10;
        Time=[ 1:33:31 PM];
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                CREATE(xmlDoc);
                xmlMgt.SetNormalCase;
                xmlProcessingInst:=xmlDoc.createProcessingInstruction('xml','version="1.0" encoding="UTF-8" standalone="yes"');

                CurrNode := xmlDoc.appendChild(xmlProcessingInst);
                CurrNode := xmlDoc.createElement('soapenv:Envelope');
                CurrNode := xmlDoc.appendChild(CurrNode);


                xmlMgt.AddAttribute(CurrNode,'xmlns:soapenv','http://schemas.xmlsoap.org/soap/envelope/');
                xmlMgt.AddAttribute(CurrNode,'xmlns:mbs','http://www.microsoft.com/mbs/xml');

                xmlMgt.AddElement(CurrNode,'soapenv:Header','','soapenv',NewChild);
                  CurrNode:=NewChild; //One level deeper
                  xmlMgt.AddElement(CurrNode,'soapenv:Body','','soapenv',NewChild);
                       CurrNode:=NewChild; //one level deeper
                       xmlMgt.AddElement(CurrNode,'mbs:enumeration','','mbs',NewChild);
                            CurrNode:=NewChild; //one level deeper
                            xmlMgt.AddElement(CurrNode,'mbs:table','Customers','mbs',NewChild);

                         recCustomer.SETRANGE("No.", '10000','20000'); //Filter only few records
                         IF recCustomer.FINDFIRST THEN BEGIN
                           REPEAT
                            vName   :=recCustomer.Name;
                            vNo     :=recCustomer."No.";
                            vContact:=recCustomer.Contact;
                              recCustomer.CALCFIELDS("Balance (LCY)");
                              vBalance:= FORMAT(recCustomer."Balance (LCY)");
                            vSPcode :=recCustomer."Salesperson Code";


                            xmlMgt.AddElement(CurrNode,'mbs:Customer','','mbs',NewChild);
                                  CurrNode1:=NewChild; //One level deeper, but keep current level too
                                  xmlMgt.AddElement(CurrNode1,'mbs:CustomerAuthentication','','mbs',NewChild);
                                         CurrNode2:=NewChild; //One level deeper to sublevel
                                         xmlMgt.AddElement(CurrNode2,'mbs:No',vNo,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:Name',vName,'mbs',NewChild);

                                  xmlMgt.AddElement(CurrNode1,'mbs:CustomerData','','mbs',NewChild);
                                         CurrNode2:=NewChild; //One level deeper to sublevel
                                         xmlMgt.AddElement(CurrNode2,'mbs:Balance',vBalance,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:SalespersonCode',vSPcode,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:Contacts','','mbs',NewChild);
                                             CurrNode1:=NewChild;//One level deeper
                                             xmlMgt.AddElement(CurrNode1,'mbs:Contact',vContact,'mbs',NewChild);


                            CLEAR(vName);
                            CLEAR(vNo)  ;
                            CLEAR(vContact);
                            CLEAR(vBalance);
                            CLEAR(vSPcode);

                           UNTIL recCustomer.NEXT=0;


                           xmlDoc.save('D:\xmlFile.xml');
                           CLEARALL;
                           MESSAGE('xmlFile.xml is created');
                         END;
              END;

      }
      CODE
      {
        VAR
          xmlDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v6.0'.DOMDocument";
          CurrNode@1003 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          CurrNode1@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          CurrNode2@1013 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          NewChild@1004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          xmlProcessingInst@1001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF89-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMProcessingInstruction";
          xmlMgt@1002 : Codeunit 6224;
          "---- Variables----"@1006 : Integer;
          recCustomer@1012 : Record 18;
          vName@1007 : Text[30];
          vNo@1008 : Text[30];
          vContact@1009 : Text[30];
          vBalance@1010 : Text[30];
          vSPcode@1011 : Text[30];

        EVENT xmlDoc@1000::ondataavailable@198();
        BEGIN
        END;

        EVENT xmlDoc@1000::onreadystatechange@-609();
        BEGIN
        END;

        BEGIN
        END.
      }
    }

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

    And can be read by codeunit: 

    OBJECT Codeunit 50050 xml read
    {
      OBJECT-PROPERTIES
      {
        Date=03/24/10;
        Time=12:25:26 PM;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                ffile.OPEN('D:\XmlFile.xml'); //this must be your file name
                ffile.CREATEINSTREAM(strInStream);

                IF ISCLEAR(xmldomDoc) THEN CREATE(xmldomDoc);

                xmldomDoc.load(strInStream);

                xmlNodeList1 := xmldomDoc.getElementsByTagName('mbs:Customer');

                ii:=xmlNodeList1.length();

                FOR i:=0 TO xmlNodeList1.length()-1 DO BEGIN
                 xmldomElem1:= xmlNodeList1.item(i); //mbs:Customer
                 IF xmldomElem1.hasChildNodes() THEN
                    BEGIN
                      xmlNodeList2:= xmldomElem1.childNodes();
                      IF NOT ISCLEAR(xmlNodeList2) THEN
                      xmldomElem2:= xmlNodeList2.item(0); //mbs:CustomerAuthentication
                      IF NOT ISCLEAR(xmldomElem2) THEN
                      IF xmldomElem2.hasChildNodes() THEN
                         BEGIN
                         xmlNodeList3:= xmldomElem2.childNodes();
                         IF NOT ISCLEAR(xmlNodeList3) THEN
                          xmldomElem3:= xmldomElem2.firstChild();//mbs:No
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtNo:=xmldomElem3.text();
                         xmldomElem3:=xmlNodeList3.item(1); //mbsName
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtName:=xmldomElem3.text();

                      xmldomElem2:= xmlNodeList2.item(1); //mbs:CustomerData
                      IF NOT ISCLEAR(xmldomElem2) THEN
                      IF xmldomElem2.hasChildNodes() THEN
                         BEGIN
                         xmlNodeList3:= xmldomElem2.childNodes();
                         IF NOT ISCLEAR(xmlNodeList3) THEN
                          xmldomElem3:= xmldomElem2.firstChild();//mbs:Balance
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtBalance:=xmldomElem3.text();
                         xmldomElem3:=xmlNodeList3.item(1); //mbsSalesPersonCode
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtSPcode:=xmldomElem3.text();
                         END;

                         xmldomElem3:=xmlNodeList3.item(2); //mbs:Contacts
                         IF NOT ISCLEAR(xmldomElem3) THEN
                            txtContact:=xmldomElem3.text();

                     END;
                END;
                      MESSAGE('This is record "%1"\No "%2"\Name "%3"\Contact "%4"\Balance "%5"\Salesperson code "%6"\of Total "%7"',
                      FORMAT(i+1),
                      txtNo,
                      txtName,
                      txtContact,
                      txtBalance,
                      txtSPcode,
                      ii);

                END;
                ffile.CLOSE;
                CLEARALL;
              END;

      }
      CODE
      {
        VAR
          xmldomDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v6.0'.DOMDocument";
          xmlNodeList1@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmlNodeList2@1017 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmlNodeList3@1019 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmldomElem1@1007 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          xmldomElem2@1010 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          xmldomElem3@1011 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          txtNo@1001 : Text[30];
          txtName@1002 : Text[30];
          txtContact@1009 : Text[30];
          txtBalance@1012 : Text[30];
          txtSPcode@1013 : Text[30];
          ffile@1003 : File;
          strInStream@1004 : InStream;
          i@1006 : Integer;
          ii@1018 : Integer;

        EVENT xmldomDoc@1000::ondataavailable@198();
        BEGIN
        END;

        EVENT xmldomDoc@1000::onreadystatechange@-609();
        BEGIN
        END;

        BEGIN
        END.
      }
    }

    That's all
    Special thanks to
    Rainer Kuhnlein

    Gedas Busniauskas (gediminb)
    Microsoft Customer Service and Support (CSS) EMEA

     

Page 4 of 49 (731 items) «23456»