September, 2012

  • Microsoft Dynamics NAV Team Blog

    Example of How to use SQL Tracing Feature to Profile AL Code

    • 7 Comments

    Enabling Tracing in Microsoft Dynamics NAV 2013

    Microsoft Dynamics NAV 2013 has a feature that allows you to see the AL call stack for a SQL commands. Here I am going to describe how it can be used to profile your application code.

    There are multiple steps required to start tracing.

    First, you need to start the Session List page. This is the same page that you need to open to start the debugger. So you need to start the development environment, then go to Tools, Debugger, Debug Session. You will get the Session List page.

    This window contains Start Full SQL Tracing/Stop Full SQL Tracing buttons. As well there is a SQL Tracing editable check box on each line.

    Start Full SQL Tracing/Stop Full SQL Tracing enables/disables tracing for all new and existing sessions and the SQL Tracing check box enables/disables tracing for a given session.

    Let’s assume we want to profile one of the sessions. Then we need to enable tracing for it, for example by clicking the check box.

    Configuring SQL Profiler

    The important part here is to select appropriate events. In this case we are interested in seeing SQL statements’ text. To achieve that we need to enable SP:StmtCompleted and SQL:BatchCompleted events. The setup should be like on the following picture.

    It allows seeing SQL queries for all statements issued from the AL.

    After this if you do some operations in the client, for example open the Sales Orders page, you will see comments in SQL Server Profiler.

    This is an example of what you can get.

    All SQL statements in-between consecutive comments correspond to the AL statement from the first comment. For example in previous screenshot, CALCFIELDS issues six SQL queries.

    The SQL profiler will contain Get connection from the pool and Return connection to the pool comments.

    These comments correspond to the events when the connection is retrieved and returned to the Microsoft DynamicsNAV Server connection poll respectively. These comments are needed to separate SQL queries issues from different clients on the same SQL connection. The SQL statement that corresponds to these comments is issued by Microsoft Dynamics NAV Server but not originated from AL.

    Comments that contain only user name also correspond to SQL statements issued by Microsoft Dynamics NAV Server but not originated from AL. For example Microsoft Dynamics NAV Server executes queries to calculate calculated fields shown on the fact boxes. We need to have this type of comments because Microsoft Dynamics NAV Server might execute an SQL query without returning connection to the pool and not originated from AL.

    Filtering Your Statements

    In the SQL profiler you will see statements from the different connections. This is because you can have multiple clients running or, for example, you can have SQL reporting services or some other service enabled. It is important to filter out everything except what is coming from the client you are profiling.

    To do that for each SQL statement you need to find first previous comment with the same SPID. If this comment is Return connection to the pool then this SQL statement is not originated from the AL code of the client that is being profiled.

    User name in the comment identifies the client by which SQL statement is generated.

    Collecting the Data and Analyzing

    Before the profiler is started, the server should be "warmed up," otherwise there is going to be a lot of queries for metadata reading. The scenarios that are going to be profiled should be executed previously at least once.

    After the SQL trace is collected it should be saved into the SQL table.

    Bellow I have an example of an SQL script that finds the most expensive AL statements. The trace was saved into the NAV7_Trace table in the master database.

    SELECT * FROM [master].[dbo].[NAV7_Trace] --query the trace table content

    DECLARE @ApplicationName NVARCHAR(100)
    DECLARE @GetConnection NVARCHAR(100)
    DECLARE @ReturnConnection NVARCHAR(100)
    DECLARE @ContainsUserName NVARCHAR(100)
    DECLARE @EmptyCallStack NVARCHAR(100)

    SET @ApplicationName = 'Microsoft Dynamics NAV Service'
    SET @GetConnection = '%Get connection%'
    SET @ReturnConnection = '%Return connection%'
    SET @ContainsUserName = '%User: Your user name%'
    SET @EmptyCallStack = '/*__User: Your user name__*/'

    IF OBJECT_ID('tempdb..#ProfilerData') IS NOT NULL
     DROP TABLE #ProfilerData

    SELECT * INTO #ProfilerData FROM
    (
     SELECT
      [RowNumber] AS [SqlStatement RowNumber],
      [TextData] AS [SQL Statement],
      [Reads],
      [Writes],
      [Duration],
      [StartTime],
      [EndTime],
      [SPID]
     FROM [master].[dbo].[NAV7_Trace]
     WHERE
      [ApplicationName] = @ApplicationName and
      [TextData] not like @ContainsUserName and
      [TextData] not like @GetConnection and
      [TextData] not like @ReturnConnection and
      [TextData] not like @EmptyCallStack
    ) SqlStatement
    CROSS APPLY
    (
     SELECT TOP 1
      [RowNumber] AS [Stack RowNumber],
      [TextData] AS [Call Stack]
     FROM [master].[dbo].[NAV7_Trace]
     WHERE
      [SPID] = SqlStatement.[SPID] and
      [RowNumber] < SqlStatement.[SqlStatement RowNumber] and
      [ApplicationName] = @ApplicationName and
      [TextData] like @ContainsUserName
     ORDER BY [RowNumber] DESC
    ) AS Stack

    SELECT * FROM #ProfilerData --this table contains mapping of SQL statements to the AL call stack

    SELECT
     CAST([Call Stack] AS NVARCHAR(4000)) AS [Call Stack],
     SUM(Duration) AS [Sum Duration],
     AVG(Duration) AS [Average Duration],
     MIN(Duration) AS [Min Duration],
     MAX(Duration) AS [Max Duration],
     SUM(Reads) AS [Sum Reads],
     SUM(Writes) AS [Sum Writes]
    FROM #ProfilerData
    GROUP BY CAST([Call Stack] AS NVARCHAR(4000))
    ORDER BY [Sum Duration] DESC -- this query finds the most expensive AL statements

    Result of previous query shows the most expensive AL calls. Second and fifth rows show the total time spent in the SQL calls issued by the server and not originated from AL.

    Also you can create a query which finds SQL statements which correspond to appropriate call stacks.

    SELECT * FROM #ProfilerData
    WHERE [Call Stack] like '%"Sales-Post"(CodeUnit 80).OnRun(Trigger) line 1556%'

    SELECT * FROM #ProfilerData
    WHERE [Call Stack] like '%"Gen. Jnl.-Post Line"(CodeUnit 12).InsertGLEntry line 59%'

    SELECT * FROM #ProfilerData
    WHERE [Call Stack] like '%"Item Jnl.-Post Line"(CodeUnit 22).ApplyItemLedgEntry line 252%'

    It is also easy to create a query which will count the number of times the same call stack occurs in the trace.

    SELECT
     COUNT(CAST([TextData] AS NVARCHAR(4000))) AS Count,
     CAST([TextData] AS NVARCHAR(4000))
    FROM [master].[dbo].[NAV7_Trace]
    WHERE
     [ApplicationName] = @ApplicationName and
     [TextData] like @ContainsUserName and
     [TextData] not like @GetConnection and
     [TextData] not like @ReturnConnection and
     [TextData] not like @EmptyCallStack
    GROUP BY CAST([TextData] AS NVARCHAR(4000))
    ORDER BY COUNT(CAST([TextData] AS NVARCHAR(4000))) DESC

    -Dmytro Sitnik
     

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2013 NAS Services User Credentials

    • 7 Comments

    You might wonder what user credentials are used to run the NAS services on Microsoft Dynamics NAV 2013.

    The NAS services start when Microsoft Dynamics NAV Server starts, unconditionally of any users logging in to Microsoft Dynamics NAV server.

    The credentials that are used to run the NAS services are the user account that runs Microsoft Dynamics NAV Server.

    In a default installation the following user name is used: NT AUTHORITY\NETWORK SERVICE

    Please note the security note about the credentials used to run Microsoft Dynamics NAV Server described in this How to: Create a Microsoft Dynamics NAV Server Instance (http://msdn.microsoft.com/en-us/library/hh168936(v=nav.70).aspx) help topic, where we recommend, for security reasons, that you always run Microsoft Dynamics NAV Server instances under a dedicated domain user account in production installations.

    The user credentials that are used to run the NAS services must be created as an NAV user, and that user must be assigned the necessary permissions in order to execute the C/AL code in the NAS Services.

    Example Useful in Demo Installations

    NT AUTHORITY\NETWORK SERVICE created as a NAV user and assigned the SUPER Permission set:

    If you haven’t created the NAV user, Microsoft Dynamics NAV Server will exit with this information in the event log:

    Server instance: DynamicsNAV70
    Session type: Nas
    Session ID: 0
    The Microsoft Dynamics NAV Server instance "DynamicsNAV70" did not start because the NAS session did not start. The user "NT AUTHORITY\NETWORK SERVICE" that is configured to start the NAS session was not identified as a user that has permissions for Microsoft Dynamics NAV. Set up a Microsoft Dynamics NAV user to run the NST, or create the NST user, such as NT AUTHORITY\NETWORK SERVICE, as a Microsoft Dynamics NAV user, and then start the NST again.
    Type: Microsoft.Dynamics.Nav.Types.NavDatabasePasswordException
    Message: The user ID and password are not valid. Please try again.

    When the user hasn’t the needed permissions the Microsoft Dynamics NAV Server will exit with this information in the event log..:

    Session type: Nas
    Session ID: 1
    The Microsoft Dynamics NAV Application Server session has failed. Reason:
    Type: Microsoft.Dynamics.Nav.Types.NavPermissionException
    Message: You do not have the following permissions on CodeUnit ApplicationManagement: Execute

    I hope this help you utilize the Microsoft Dynamics NAV 2013 NAS services. :-)

    Thanks,

    Hans Kierulff

  • Microsoft Dynamics NAV Team Blog

    Release Candidate for Microsoft Dynamics NAV 2013

    • 7 Comments

    Hi, everyone, I hope you are doing well and having a great day!
     
    Just this week, we cut our first release candidate (“RC1”) for NAV 2013, which means we’re very close to RTM and shipping the product to you. I can’t say enough about this product and how it’s come together. But I’ll certainly try, since it’s so much fun to talk about!

    Dynamics NAV 2013 has powerful and diverse new application functionality, ranging from charting to cash flow analysis to time sheet registration to assembly to expanded out-of-the-box RoleCenters to Unicode. Have to catch my breath for a second …  The user experience innovations range from the SharePoint and web clients to end user productivity features on the rich client to improved integrations with OneNote and Excel. From all the feedback we’ve gotten, even diehard classic client fans will be on RoleCenters very soon! The runtime investments increase performance and scalability for transactions and reporting. They’re also built to run optimally on Azure as well as to improve partner-hosted performance. To improve manageability we created a new admin console and added APIs for powerful scripting. The server also now complies with the rigorous Microsoft’s Common Engineering Criteria (CEC) server “hygiene” standards. In response to your needs for developing on pages, we’ve enhanced tooling substantially around page design and debugging (and testing!). Finally, we’ve rethought rapid deployment through the new Rapid Start module. That’s just off the top of my head! Perhaps, more than anything, this release has been about quality: NAV 2013 exceeds every quality bar for any release of NAV we’ve had thus far. I guess that’s what I’m proudest about. 

    Together, this array of functionality and capability manifests our value proposition for the release and the product – Microsoft Dynamics NAV 2013 is “A Business Solution from Microsoft.”

    I hope you go to PartnerSource later this month, download the product, and get to know it better. Then, go aggressively sell it to new customers and upgrade your existing customers!

    Thanks to all the people who’ve made NAV 2013 happen, both externally as partners and customers providing feedback through our Technical Adopter and Beta programs and internally on the Microsoft development team. Collectively, you’ve all done an amazing job getting out a great product.
     
    It’s an exciting time to be in the NAV business and for the NAV product. From the tremendous energy at a sold-out Directions EMEA in Rome to the release of NAV 2013 this month to what I expect will happen at Directions in the Phoenix later this fall, I feel the momentum growing. Perhaps that’s why it’s such a bittersweet time for me personally. As some of you already know, after four years living in Denmark, my family and I moved back to the U.S. this summer to be closer to our extended family. As a result, after four and half years as the General Manager of NAV R & D, my time has come to say farewell and thank you for all the incredible memories  I’ve had with you our partners, with the Microsoft team in Vedbaek, and with the product itself. It’s been an amazing experience! Thank you all very, very much! My successor, Erik Tiden, is a person worthy of this great community, and I welcome him warmly. (I’ll let him tell his story in the next blog.)

    Best of luck to you all, and I hope our paths cross again soon.
     
    Sincerely,

    -Dan

     

  • Microsoft Dynamics NAV Team Blog

    Replacing Field Values in Microsoft Dynamics NAV 2013

    • 4 Comments

    Have you ever had the need to replace some values from a list of values?  For example, you need to change the Postal/Zip Code for a number of your customers.  Most of the time you would need to write a codeunit or report to accomplish that or even have the customer update each entry by hand.  What if you needed to do the same with your vendors?  That would require either modifying the previous codeunit/report or possibly just creating a new object to handle this or again having the customer update each entry by hand. 

    Here is another option – a Replace page that you can add to any page with just a few lines of code. 


    Fig 1. Replace Page

    Field Descriptions:

    • Selected Field – use the AssistEdit () or type the field name to select the field that you want to replace values from.
    • Find What – the value that you want to find and replace with a new value.
    • Replace With – the value you want to replace the original value with.

    Options:

    • Match – you can either match the “Whole field” or “Any part of the field”.
    • Match Case – allows you to match only if the case matches exactly.
    • Replace Whole Field – allows you to replace the whole contents of the field with the new value.
    • Records in Data Set – this is an indicator as to how many records are in your current filtered dataset.

    How to implement the page:

    If you import the attached object text file and follow these steps you will be able to add this to almost any page (at least where it makes sense).  For this example I will be updating the Customer List (Page 22).

    1. Import and compile the attached object “Replace NAV2013.txt”.

    2. Design the Customer Page (21).

    3. Add a global variable named Replace and point it to Page 50050 (or whatever you may have changed it to prior to import)

    4. Create a new action.

    5. Go to the OnAction trigger for your new action and add the following code …

    6. Compile your object and you should be ready to use it.

     

    How it works:

    • The function LoadDataSet simply loads the appropriate filtered dataset.
    • The SetValidations function is optional.  If you are going to run the Replace page from a page that needs to run validations then you add the SetValidations function.  There are 2 parameters – ModifyLevel determines whether the modify trigger is run for that update.  The FieldLevel parameter is used to enable the field OnValidate triggers to be run.  The Field Level parameter is for all fields.  There is no way (at this point) to determine which fields will run the OnValidate and which ones won’t.

    Note:  When use this Replace page, keep in mind that the TIME and DATETIME fields are very sensitive from a matching perspective.  Even though the TIME may only appear to be HH:MM:SS on the page, there will probably be a millisecond component to the TIME as well.  So, to have the best luck, it would probably be best to copy the value of the cell that you want to replace and use that as the value in “Find What” field.  This is true with the DATETIME fields as well.

     

    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

    Introducing Erik Tidén, General Manager, Microsoft Dynamics NAV R&D

    • 2 Comments

    Hello NAV community,

    Please allow me to introduce myself: My name is Erik Tidén, and it is an honor and a privilege for me to take on the role of General Manager, Microsoft Dynamics NAV R&D. I am excited about the prospects of working with all of you who are involved with NAV. It is a great product with a community that enjoys an industry-wide reputation for passion and excellence. I consider myself fortunate to be joining this tradition and to have the opportunity to take NAV to even greater heights together with all of you.

    What do I bring to the party?

    You might ask what I will be bringing to the NAV community. Well, after 20 years of experience in business software solutions, I am passionate about building great software. My recent experience includes executive roles at HP and at SAP, where I was General Manager and Global Head of Custom Development. Prior to that, I held the position of Senior Vice President for SAP CRM Development, where I was responsible for the software development of SAP’s award winning CRM 7.0 product.

    What do I want from the role?

    Working closely with customers and partners is a big part of why I have found my past roles so rewarding. I especially enjoy working with small and medium-sized companies. They are every bit as sophisticated as large enterprises in their business requirements, yet they have the added challenges of simplicity and ultra-low TCO.

    The heart of NAV

    If Vedbaek is the home of NAV, then you could say that I have already entered the NAV community, having recently moved to Denmark with my wife. The heart of NAV, however, is not in one geographical location, but wherever the partners and customers work with the product around the world.

    There will be many opportunities for me to meet with you, the people of the NAV community soon, and I look forward to my journey starting with Directions US in Phoenix, Arizona on October 27th-30th.

     

    Sincerely,

    -Erik Tidén

    General Manager, Microsoft Dynamics NAV R&D

  • Microsoft Dynamics NAV Team Blog

    Crash Dump Creation

    • 0 Comments

    Actually, NAV should never crash, but sometimes there are circumstances that cause a crash of one of the NAV processes or services. For the user and the administrator, it is usually not clear why a component crashes. Most of the time the application event log does not show enough information to determine root causes of the crash.

    A "crash dump" provides very good information about the module and code which caused the crash. The crash dumps can be analyzed by Microsoft Dynamics NAV support and help a lot to find the problem.

    With the "Debug Diagnostic Tool v1.1. 2" it is very easy to create the crash dump files in case of a crash.

    The tool can be downloaded under the following link: http://www.microsoft.com/en-us/download/details.aspx?id=26798

    Note: If the operating system is not configured for "en-us" for region and language, then the following steps are necessary to install the Debug Diagnostic
    Tool v1.2:

    1. Create a local group named "Users" (via Control Panel-> Administrative Tools -> Computer Management -> Local users and groups).
    2. Add yourself to this local group.
    3. Grant full access to this group to DebugDiag installation folder.
    4. Install DebugDiag.
    5. You will get an error removing the backup files!
    6. During this error - access the install directory (as administrator) while the installation is running and the error is shown.
    7. Copy the files to another directory (e.g. C:\Temp\DebugDiagSave).
    8. Complete the registration (failed - files deleted).
    9. Create the folder DebugDiag again on c:\Program Files (if you use x64 or the program folder for x86).
    10. Copy the files back to the installation directory.
    11. Run the installation once again.
    12. Run Register.bat from the installation folder to register all COM objects.

    After you install and run the application, you are welcomed by the "Rule Wizard."

    For the crash dump creation, you choose Crash and then choose the Next button. Following is the selection of what you would like to check. For NAV, it is necessary to select "a specific process":

    The next selection window shows all processes. Here, you can select, for example, "Microsoft.Dynamics.NAV.Client.exe".

    With "Advanced Configuration" it now possible to set the granularity of process monitoring.

    The next screen shows the path where the crash dump is saved. You must select a hard disk with enough capacity. Crash dumps can be very large in some circumstances (e.g. if you selected "Full Userdump" in the "Advanced Configuration" screen).

    In a last step the rule must be activated:

    Once the created rule was activated, "Debug Diag" monitors the configured process.

    In case of a crash, "Debug Diag" creates now the necessary .dmp files in the specified folder and additional log files in the installation folder of "Debug Diag" (e.g. "C:\Program Files\DebugDiag\Logs").

    I hope the described steps help you to create the important crash dump information.

    Frank Wurzel

    Microsoft Dynamics Germany

Page 1 of 1 (6 items)