Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

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

    • 11 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

    Using XMLports With Web Services

    • 11 Comments

    As a follow-up on my recent webcast (found HERE), here is the general walkthrough of how to create an XMLport and use it for sending data to NAV.

    First, what we want to do is create our XMLport and make sure it has the elements and values that we want.

    XMLPort

    For the root element, I have set maxOccurs = 1 to avoid any confusion.
    For the general XMLport, the UseDefaultNamespace and the DefaultNamespace values have been edited as seen below.

    SS02

    Other than that, I have no code on my XMLport, but naturally, anything goes that would work on a regular XMLport. Now to the Codeunit:


    ImportDim(VAR DimImport : XMLport DimImport) Return : Text[30]
    DimImport.IMPORT;
    EXIT('Import Run');

    So basically we’re telling the XMLport to run an import and we’re returning to the Web Service that we’ve run. All we need to do now is expose the Web Service using Form 810:

    SS03

    Remember that the actual name of the codeunit does not have to match that of the service name here.

    So now we move over to Visual Studio and start working with what we have. The first thing we’ll notice is that the WSDL matches our XMLport.

    SS04

    What we see is both the RootDimensions element which consists of multiple Dimension elements. From there, we can see the definition of the Dimension element the fields we’ve chosen to expose.

    When creating a new project, we will go with a Windows Forms project this time.

    SS05

    And from there we will start off by adding a web reference to http://localhost:7047/DynamicsNAV/WS/Codeunit/DimensionImport .

    The details on how to add a web reference can be found in the Developer and IT Pro Documentation.

    On my new form, I have created two input boxes for the Code and Name of the dimension and a Create button.

    SS06

    And then we have the code on the Create button, along with helpful comments:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms; 

    namespace
    NAV2009SP1WSDemo

        using WSDI; 
        public partial class Form1 : Form 
        {    

     

     

     

            public Form1() 
           
                InitializeComponent(); 
           
     
            private void button1_Click(object sender, EventArgs e) 
           
                //Make sure the ResultLabel doesn't have any text on multiple runs 
                ResultLabel.Text = ""

               
    //Create a WS reference instance, set credentials and define the company by specifying the URL. 
                DimensionImport NAVImport = new DimensionImport(); 
                NAVImport.UseDefaultCredentials = true
                NAVImport.Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20International%20Ltd/Codeunit/DimensionImport"
                
                //First we create our root element 
                RootDimensions RootDim = new RootDimensions();

     

     

     

                //Then we create a List to handle our (possible) multiple dimensions 
                List<Dimension> DimList = new List<Dimension>();

     

     

     

                //And then we create a single dimension 
                Dimension Dim = new Dimension();  

     

                if (dimInputCode.Text != "" && dimInputName.Text != ""
               
                    //We assign the values from our textboxes to the single dimension 
                    Dim.DimensionCode = "AREA"
                    Dim.Code = dimInputCode.Text; 
                    Dim.Name = dimInputName.Text;

     

     

     

                    //Then we add the single dimension to our list 
                    DimList.Add(Dim);  

     

                    //To get the list of dimensions "attached" to the root element, we use the following
                    RootDim.Dimension = DimList.ToArray(); 
                    try 
                   
                        //Then we send to NAV and show our result 
                        ResultLabel.Text = NAVImport.ImportDim(ref RootDim);

     

     

     

                   
                    catch (Exception ex) 
                   
                        //Show a possible exception 
                        ResultLabel.Text = ex.ToString(); 
                   
               
                else 
               
                    //Make sure there are values 
                    ResultLabel.Text = "Both values must be filled"
               
            
        }
    }

    Our wonderful application is now ready to run and all we have to do is press F5:

    SS07

    We have now created our XMLport, exposed it using a codeunit and set data into it from a form based application.

    Lars Thomsen

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Setting Up a Mail Template for Document Approval Notifications

    • 11 Comments

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

     

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

     

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

    default template

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

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

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

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

     

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

     

    To Set Up a Mail Template for Document Approval Notifications

     

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

     

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

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

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

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

     

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

     

    1.      Locate the exported template file.

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

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

    4.      Save and close the HTML file.

     

    Finish by importing the changed template.

     

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

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

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

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

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

     

    Overdue Mail Template

     

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

     

    - Soren Groes-Petersen

  • Microsoft Dynamics NAV Team Blog

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

    • 11 Comments

    Cumulative update 8 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

    Cumulative update 8 also introduces new functionality for exporting and importing companies and other data. You can export a company from a Microsoft Dynamics NAV database and import it into another database, and you can export and import other types of data such as global data, application data, and application objects.

    In earlier versions of Microsoft Dynamics NAV, you exported and imported this type of data as part of backing up and restoring databases. In Microsoft Dynamics NAV 2013 R2 CU8, you can do this by using the Export-NAVData and Import-NAVData Windows PowerShell cmdlets. You can also import and export data in the Microsoft Dynamics NAV Windows client.

    For more information, see the following documents on the W1 version of the Microsoft Dynamics NAV 2013 R2 CU8 download media:

    • MicrosoftDynamicsNAV2013R2CU8_ExportImportDataUsingNavDataFiles.pptx
    • MicrosoftDynamicsNAV2013R2CU8_ImportExportData.pdf

    Please note that there is very limited support for exporting and importing data in the Microsoft Dynamics NAV Web client. Use the Microsoft Dynamics NAV Windows client or the Windows PowerShell cmdlets instead.

    Where to find cumulative update 8

    You can download cumulative update 8 from KB 2971746 – Cumulative Update 8 for Microsoft Dynamics NAV 2013 R2 (Build 36897).

    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 2015

    • 11 Comments

    On September 14th, we presented Microsoft Dynamics NAV 2015 to 600 partner representatives at Directions US, and we are proud to announce that General Availability of Microsoft Dynamics NAV 2015 will be October 1st 2014. Already today partners can download Microsoft Dynamics NAV 2015 from PartnerSource and start investigating the new functionality. Visit the new readiness page to access training and resources about Microsoft Dynamics NAV 2015, and download the product here. For more information, see the Microsoft Dynamics NAV Help in the MSDN Library.

    Partners who did not attend Directions US and want to learn more about Microsoft Dynamics NAV 2015, explore business opportunities, and network with other partners should sign-up for Directions EMEA event October 8-10 in Poznan, Poland. Make sure to check back here on The NAV Team Blog for more updates in the coming weeks.

    Best regards,

    The Dynamics NAV team

  • 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

    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

    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

    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

    Update Rollup 4 for Microsoft Dynamics NAV 2013 R2 has been released

    • 9 Comments

    Update rollup 4 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2 and includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU – Australia
    • DE - Germany
    • DK - Denmark
    • FR - France
    • IT - Italy
    • NA – North America
    • NL – Netherlands
    • NZ – New Zealand
    • SE - Sweden
    • UK - United Kingdom

     

    New in update rollup 4 

    Beginning with update rollup 4, the update rollups for Microsoft Dynamics NAV 2013 R2 now also includes hotfixes for the upgrade toolkit. 

     

    Where to find update rollup 4

    You can download update rollup 4 from KB 2930617 - Update Rollup 4 for Microsoft Dynamics NAV 2013 R2 (Build 36078).

    The hotfixes that have been released since update rollup 3 are listed in KB 2930617. For a full list of all hotfixes included in the update rollup, see the following CustomerSource and PartnerSource pages: 

    CustomerSource:

    PartnerSource

     

    More Information

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

  • Microsoft Dynamics NAV Team Blog

    Memory usage in Microsoft Dynamics NAV 2013 print preview

    • 9 Comments

    Periodically we receive requests where customers asking us about RTC print preview consumes all available memory and computers hangs.

    Repro scenarios are more/less similar: run report, click preview, go per pages up/down, close report. Run another report, click preview, go per pages up down and… RTC hangs.
    If we look to memory usage, NAV is using all available memory.

    If we analyze what has happened, we see: with every move per pages memory usage increase and increase and increase until all memory is used.
    It looks like typical memory leak because in NAV 2009 with the same repro everything is OK.

    However it is not so simple. What NAV does in this scenario is: it loads Microsoft Report Viewer 2010 with 2 files: report definition (RDL file where is described report structure) and record set. And that is all, next actions like preview/print/export are managed by report viewer. The same we have in NAV 2009 just it loads Report Viewer 2008 and .NET Framework 3.5 as NAV 2013 uses Report Viewer 2010 and .NET Framework 4.5. And here is reason pointing us to memory usage behavior: Report Viewer 2010 and .NET 4.5 uses another way to manage memory (named more advanced and more secure). However this ‘another’ way gets us mentioned questions from customers and really we can do nothing from NAV platform side – system works as it is designed to work.

    But things are not so bad. In .NET memory is managed very smart and not used memory is released by function named ‘garbage collector’. This function periodically review memory blocks and release not used. And actually it works: if we monitor memory during report previewing and after it close we see that in preview and going per pages memory usage increase, after preview close memory usage is still the same, but after 5-10 min used memory decrease to initial numbers. For example in my tests started RTC using 98.5 MB, after few previews and scrolls and etc. usage increases to 215 MB and doesn’t decrease if I close preview. But after few minutes garbage collector returns usage to 100.4 MB.
    We can force (in NAV platform) garbage collector to work faster and memory release will be faster, but this will increase processor usage and in many cases it is worse than memory usage.

    Few trick could help users do not meet memory problems:

    1. When we open report preview we see report in “funny” interactive mode. This is preview mode where we can change report view in some way described in report code. For example in report ‘Customer – Top 10 List’ sorting can be changed. But exactly this mode is memory monster. If we click ‘Print layout’ button in menu we come to ‘page view’ where we can change nothing in report and see exactly what will be printed. This mode doesn’t use memory so much, so we can use this view easier. Few additional points here: a) all report initially open in interactive mode so users need manually change mode; b) Mode is not available if report is not allowed to preview like Sales Invoice or Order Confirmation.
    2. Report run SAVEASPDF works better if we call it directly without request form. However then users need to do more actions to open report. But maybe this can be reasonable in some cases.
    3. And finally if there are requirements to run big reports with many pages (100 and more) then better is to run them on server side, just because server is 64 bits and usually has more available RAM. This can be done by creating codeunit which runs report SAVEASPDF. Then add codeunit to job queue. Job queue executes codeunit and create pdf file.

     

    New Information since Update Rollup 5:

     

     

     

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

     

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

     

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV and SQL Server Database Compression

    • 9 Comments

    Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise Edition) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases. Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition. Compression can also reduce disk utilization and sql memory utilization as dead is stores on disk in a compressed state and also reads in the SQL cache in a compressed state. Compression can add 10 - 30% percent increased CPU utilization depending on what tables and indexes are compressed and what level of compression is used.

    For Dynamics NAV we recommend only compressing tables and indexes that have a read to write ration of 80%/20% (This is a conservative threshold) or higher as compressing tables with a higher write ratio can actually decrease performance. We also recommend using ROW compression if the space saving between ROW and PAGE level compression is less that 10%; if the difference is over 10% then we recommend PAGE compression. This is because if the space savings from PAGE compression is close to or similar to ROW compression then it is not recommended to incur the additional overhead associated with PAGE compression. An example of NAV tables that would benefit greatly from compression are the large "Entry" tables such as G/L Entry, Value Entry, and Item Ledger Entry. An example for NAV tables that would not benefit from compression and where performance may actually decrease due to compression are "Line" tables where the data is temporary in nature such as Sales Line, Purchase Line, and Warehouse Activity Line. SQL Server compression is completely transparent to the Dynamics NAV application.

    Compression is done on Table or Index basis. On tables with clustered indexes the clustered index is the table so compressing the clustered index is equal to compressing the table. Running the ALTER TABLE.. WITH COMPRESSION is only necessary on HEAPS for all tables with Clustered Indexes you can use the ALTER INDEX.. WITH COMPRESSION.

    How to determine the read/write ratio of an index? Luckily SQL keeps track of this for us and all we need to do is extract this data. SQL Server stores this information in the sys.dm_db_index_operational_stats DMV. Remember DMV's are "recycled" each time the SQL Server service is restarted so if SQL has only been up and running for a day or a week this information will be of minimal use. Ideally you would want the server to be up any running for several weeks and through a month end close to get a true idea of actual data access patterns of your database.

    The following query will tell you how long the SQL Server instance has been up and running:

    select 'Sql Server Service has been running for about '

           + cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and '

           + cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours'

           from sys.databases where name = 'tempdb'

    The following query will give the approximate read write balance of all the used indexes in the database.

    SELECT o.id, 

                            o.name,

                            x.name,

                            i.index_id,

                            x.type_desc,

                            s.rowcnt,

                            i.leaf_update_count * 100.0 /

                                  (i.range_scan_count + i.leaf_insert_count

                                      + i.leaf_delete_count + i.leaf_update_count

                                      + i.leaf_page_merge_count + i.singleton_lookup_count

                                  ) as Writes,

                            i.range_scan_count * 100.0 /

                                  (i.range_scan_count + i.leaf_insert_count

                                      + i.leaf_delete_count + i.leaf_update_count

                                      + i.leaf_page_merge_count + i.singleton_lookup_count

                                  ) as Reads

                  FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

                  JOIN sys.sysobjects o ON o.id = i.object_id

                  JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id =

    i.index_id

                  JOIN sys.sysindexes s ON s.id = x.object_id and s.indid = x.index_id

                  WHERE (i.range_scan_count + i.leaf_insert_count

                            + i.leaf_delete_count + leaf_update_count

                            + i.leaf_page_merge_count + i.singleton_lookup_count) <> 0

                  AND objectproperty(i.object_id,'IsUserTable') = 1

                 

    You can also run a simulation with the sp_estimate_data_compression_savings stored procedure in SQL with will run a sample of the data through the chose compression level and give you the estimate of the space saved by enabling compression.

    I strongly recommend reading the following MSDN article before engaging in database compression activities.

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

    Note: You can use the following query to check to see if there are currently any compressed indexes in the database.

    SELECT o.name Table_Name, p.index_id as Index_ID, i.name as Index_Name,

          CASE

          WHEN p.data_compression = 1 THEN 'ROW Compression'

          WHEN p.data_compression = 2 THEN 'PAGE Compression'

          ELSE 'ERROR'

          END Compression_Type

    FROM sys.partitions p

    JOIN sys.objects o ON p.object_id = o.object_id

    JOIN sys.sysindexes i ON o.object_id = i.id AND p.index_id = i.indid

    AND p.data_compression in (1,2)

    ORDER BY o.name, p.data_compression, p.index_id

     

    Michael De Voe

    Senior Premier Field Engineer

    Microsoft Dynamics

    Microsoft Certified Master - SQL Server 2008

  • Microsoft Dynamics NAV Team Blog

    About Object Metadata, and why I can't see object changes in RTC

    • 9 Comments

     

    A few times we have seen case where changes to NAV objects are not seen in RTC until the NAV Server is restarted. This post explains a likely solution to such problems. And as we are on the topic anyway, also explains a bit about the process that turns C/AL code into Metadata (c#).

     

    How object changes get to RTC


    When you save an object in Object Designer, then table 2000000071 "Object Metadata" is updated as well. An easy way to see this part of the process in action is:
    Run table 2000000071 and delete Page 1 (or any object). Then compile Page 1 from Object Designer. Then check that it has been re-created in table 2000000071.

    If you look at this table in SQL Server Management Studio, you can see that it has SQL triggers which update the table "Object Tracking". It is the "Object Tracking" table that NAV Server uses to see when an object has changed, so that it knows to send updated Metadata to RTC.

    There are two ways that the NAV Server can get updated on activity in the "Object Tracking" table:
      SQL Server Broker
      Polling

     

    SQL Broker

    If SQL Server Broker is enabled, then NAV Server will rely on that to notify it when an object has changed. You can see whether the broker is enabled from SQL Server Management Studio under Database Properties, then under Options look for "Broker Enabled".

    If the broker is enabled, then when NAV Server starts up and on first activity (When first RTC connects), it will create a queue and a service under SQL Service broker. You can see this in SQL Server Management Studio under the NAV Database if you expand Service Broker. Here, under Queues and under Services you will see new objects with names like SqlQueryNotificationService-27b7fb21-74a7-4a63-876a-c96b8eecd583. These are created by NAV Server and removed again when NAV Server stops. Their job is to listen to the "Object Tracking" table and notify NAV Server when there are any changes there.

     

    Polling

    If the broker is not enabled, then NAV Server will use polling, i.e. check for changes every now and then. You can see this in two ways. First in the Application Log the NAV Server will log this event shortly after startup:

    Service: MicrosoftDynamicsNavServer
    SQL Query Notifications are unavailable on SQL Server '.' in Database 'NAVDatabase'. The Object Change Listener has switched to polling.

    Secondly, when NAV Server is in polling mode and you start SQL Profiler you will see this query being run regularly by .New sqlClient Data Provider:

    exec sp_execute 1,@lastKnownTimeStamp=463223

    This is a pre-prepared query which looks like this:

    SELECT [Object Timestamp], [Object Type], [Object ID], [Object Key] FROM [dbo].[Object Tracking] WHERE [Object Timestamp] > @lastKnownTimeStamp',@lastKnownTimeStamp=463225

    In polling mode, this is how NAV Server checks for object changes since last time it checked, so it knows whether to send updated object definitions (metadata) to RTC.

     

    Troubleshooting

    So that's the background. How do we handle the case where object changes are not seen in RTC until we restart NAV Server? If this problem happens, then switch method from SQL Broker to Polling or visa versa. You switch by enabling / disabling the broker like this:

    ALTER DATABASE [MyNAVDatabase]
    --SET ENABLE_BROKER
      SET DISABLE_BROKER
    WITH ROLLBACK IMMEDIATE

    The "WITH ROLLBACK IMMEDIATE"-part of this query is to avoid what happened for me that the query just hang, or would only run if the database was put in single user mode.

    In the cases we have seen, the broker has been enabled but had some kind of problem. A slightly cryptic message was recorded in the SQL Server Log every time an object was changed in Object Designer. This message was not logged in the Application log. So make sure to check in SQL Server Management Studio under Management -> SQL Server Logs -> Current. If this gives enough information to solve the problem then good. If not, then there is the option of switching to polling (disable the broker) until the root of the problem can be resolved.

    Enabling the broker is the preferred option since it saves NAV Server for checking ever so often for object changes. But if the broker doesn't work then at least there is the option to disable it until any problems can be resolved.

     

    Lars Lohndorf-Larsen

    Dynamics NAV Support EMEA

     

  • 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

     

     

     

     

     

Page 4 of 50 (744 items) «23456»