Last month (23rd – 25th April), Claus Lundstrøm and I took to the road and drove all the way from Vedbæk to Netherlands and Belgium to meet one of our would-be TAP Partners and attend the launch of Belgian Dynamics Community respectively. The camaraderie and dedication of Dynamics enthusiasts in Belgium was exemplary and compelled me to write a blog entry here in our team blog. You can read a lot more details about it on Waldo’s blog post but I wanted to highlight some of our takeaways from this highly successful event.
Firstly I am reminded of my first ever Microsoft Windows NT Developers’ Conference back in 1992(!) here in Brussels, Belgium – anyone remember that? Belgium is an amazingly convenient place to get to, it has friendly people who speak multiple languages and are really glad to help you, to work with you and fast forward to 2008 this event was no exception! The event organizers had worked tirelessly at their own expense to put together the event, agenda, logistics and gather sponsors and by any measure the event was a sellout success. They registered over 250 participants and had over 200 show up despite traffic delays and Microsoft members being invited to Steve Ballmer 1:1 session the very same evening.
The event location was a picture perfect castle with cooperative weather despite some rain showers earlier. We got there for lunch, discussed logistics and parted ways in search of our hotel for the night stay (good thing we did that, as it took us nearly all the spare time we had before the event late evening to sort out mix-up with our booking and find the new hotel). By the time we came back, venue was transformed for the launch event, people were already arriving in hoards. The organizers provided meals, beverages, snacks, champagne, wine throughout the evening and kept all the participants happy. The keynote was presented by Marc Charlier who recently moved from SAP to lead Dynamics Marketing in Benelux region. I followed up with a high-level MBS Strategy / Roadmap presentation. A Coffee break followed where most audience were interested in future direction of NAV in relation to Managed Platform. I was very interested in hearing their opinions and surely got plentiful of it. Claus followed then with a detailed hands on demos of NAV 2009 product with focus on Page and Report design, customization and personalization. His usage of image fields to demo pages (with fact boxes) and interactive reports was extremely effective and audience were clearly impressed with the power and ease of use of NAV 2009.
Everyone seemed to know everyone else! It was very nice to see people mingling with one another, catching up on their projects, place of work, and life. Majority of attendees were Dynamics NAV enthusiasts but Dynamics CRM, & Dynamics AX had their followings too. What followed was a raffle and party where we received over-attention (signing away NAV books as stars) and a fare share of wining, dining and dancing till wee hours of the morning. If you would like to see pictures from the event follow this link.
Dynamics Community in Belgium is a shining example of what communities are all about - they are for the people, by the people who share a common interest. We at Microsoft are very thankful for this community’s interest in Dynamics NAV, it is an integral part of the entire echo system around MBS products as it helps nurture growth of our business organically by bringing like minded people together to share their experiences, ideas, & opinions, voice their concerns, collaborate on projects & network with one another. The Belgium Dynamics Community is off to a great start and I wish them successful years ahead.
- Naveen Garg
In previous versions, Microsoft Dynamics NAV maintains SIFT totals in SIFT tables. So updating the main table is done in one query. Updating the related SIFT tables is done by seperate queries run from triggers on the SQL tables. This makes it difficult to idenitfy the real cost of updating the table with a SQL Profiler trace, since you need to take several queries into account to get the real cost.
From NAV version 5 SP1, the SIFT tables are replaced with Indexed Views which makes it simpler to trace the full cost of an update to a base table and its associated indexed views:
In a SQL Profiler Trace, enable the event Performance:Showplan XML. With this event, when you see an update to a base table (for example INSERT INTO "W1500SP1"."dbo"."CRONUS International Ltd_$G_L Entry" etc), then the "Showplan XML"-event for this query will show not just the insert into the base table, but also which indexed views were updated, and the percentage of cost that each indexed view added to the whole query. So with this event you will have the full cost of an update and associated SIFT indexes in just one place - not spread over multiple queries in the Profiler trace.
You can read more details and screenshots of this, and other changed in NAV 5 SP1 in this post:
Changes to Microsoft Dynamics NAV 5.0 SP1 with Microsoft SQL Server
Lars Lohndorf-Larsen (Lohndorf)Escalation Engineer
When running forms like Customer (Vendor) Ledger Entries and Apply Customer(Vendor) Ledger Entries, and selecting: send-to Excel, the following error occurs if customer (vendor) name contains character '/':
Problems came up in the following areas during load:
Workbook setting ....
To correct this issue, open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder. Browse to the following section and replace the marked line :
<xsl:template match="Control[@type='TableBox']"> <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:attribute name="ss:Name"> <xsl:variable name="TableBoxCaption"> <xsl:value-of select="//Object/@caption"/> <!-- replace this line !--> </xsl:variable>
Then browse to the following section and replace the section:
<xsl:template match="Control[@type='Frame']"> <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:attribute name="ss:Name"> <xsl:value-of select="@caption"/> <!--changed line!--> <xsl:if test="@caption = ''"> <xsl:variable name="TableBoxCaption"> <xsl:value-of select="//Object/@caption"/> <!--changed line!--> </xsl:variable>
<xsl:template match="Control[@type='Frame']"> <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:attribute name="ss:Name"> <xsl:value-of select="translate(@caption,'\/:*?>|','')"/> <!--changed line!--> <xsl:if test="@caption = ''"> <xsl:variable name="TableBoxCaption"> <xsl:value-of select="translate(//Object/@caption,'\/:*?>|','')"/> <!--changed line!--> </xsl:variable>
Microsoft Dynamics NO
In C/Front for Microsoft Dynamics NAV Version 5 SP1, it is no longer possible to initialise it in the usual way from Visual Basic. Often you would initialise C/Front like this:
CFR = New Microsoft.Navision.CFront.CFrontDotNet
But with SP1, this syntax will give you the following error in Visual Studio:
Error 1 'Microsoft.Navision.CFront.CFrontDotNet.Private Sub New()' is not accessible in this context because it is 'Private'.
This way to initialise C/Front was removed because there were various problems (mainly with memory corruption) when disposing C/Front. So to stabilise it, the public constructor of CFrontDotNet was removed. Now, initialise it like this instead:
CFR = Microsoft.Navision.CFront.CFrontDotNet.Instance
Lars Lohndorf-Larsen (Lohndorf)
Microsoft Dynamics UK
It's been a busy couple of months since I last posted a blog, and I have learned a ton. Back then my stated near-term goal was to learn about the team, the product, partners and customers. During that time, I've had a lot of the feedback I asked for - feedback about what you love, what you don't love, and what you wish you could love about Dynamics NAV. I've heard it from team members, at Convergence in Orlando, at Directions EMEA in Paris, and at many other ad hoc meetings with customers and partners. Overall, it's been a wonderful experience learning about Dynamics NAV.
The main piece of feedback that I received has been, "when can I get NAV 2009"? The same thing has happened to the people asking this question as has happened to me. If you went to Convergence North America this year or attended Jan's and my keynote at Directions EMEA, you've heard about our User Experience team's aspiration to make a user interface that users love. Well, they succeeded with the Role-Tailored Client in NAV 2009. I love it! Not only does it capture the essence of NAV's core values - simplicity, ease-of-use, and flexibility, it has an elegance and beauty that is unparalleled in the industry. This is by far my favorite product to demo. As for when it will be available, we're on track to ship this fall, as I mentioned back in February. In the meantime, as a registered partner, you can download a VPC image of a CTP3 ("customer technology preview v3") today and see the accompanying video introduction on PartnerSource.
I mentioned previously that the stakeholders of this product are very passionate, something that I think really drives its overall excellence. I'd like to elaborate on that a bit. Last month, I was at Directions EMEA in Disneyland Paris. (For someone who grew up in Florida, it took some time to get used to Donald Duck and Goofy saying bon jour! ) It was there that I really understood what the NAV partner community was about. There was a buzz in the air about all the products and the companies that collectively make up the NAV ecosystem. Walt Disney said that the secret to making dreams come true is the four C's - curiosity, confidence, constancy, and courage. He especially admired courage. "When you believe a thing," he said, "believe it all the way, implicitly and unquestionably." That was courage to him. To me, Directions in both North America and EMEA – how it was established and the momentum that it’s gained – is the manifestation of Disney’s definition of courage. For all of you who have been involved in Directions, whether as founders, contributors, or attendees, thank you from the Dynamics NAV team. In fact, the four C's have been in full force in every partner or customer meeting I’ve been in, as I suspect they are everywhere people are around the world are working on the NAV ecosystem and using the NAV products.
That goes for the team at Microsoft as well. This team is excited about the product and can't wait to get it in your hands. I'm not the only one who recognizes this. In one of the videos from the "Speak Your Mind" kiosks at Convergence in March, one person said the following: "NAV offers great functionality in the product … the team shows tangible enthusiasm … they treat the audience like they were smart and significant about the business issues … Hats off to the NAV team - they were perfect!" We gushed when we saw the video. But we feel the same way about our customers and our partners, and I think that's why our enthusiasm is so evident.
Indexed views on SQL Server 2005 includes functionality like "index view matching" and is a capability that enables SQL Server (Enterprise and Developer Edition only) to automatically get the result of any query from an indexed view if it deems that this is faster than going to the base table. In other words this really makes indexed views act like indexes (A faster way to retrieve data). This functionality (index view matching) is not used in Dynamics NAV 5.0 SP1.
The benefit of index view matching is seen when we create an indexed view like: CREATE VIEW GLEntry$VSIFT1 WITH SCHEMABINDING AS SELECT AccountNo, COUNT_BIG(*) $Cnt, SUM(Amount) SUM$Amount FROM GLEntry GROUP BY AccountNo
And execute a query like: SELECT AccountNo, SUM (Amount) FROM GLEntry WHERE AccountNo='1000'
Then SQL server automatically gets the result from the indexed view rather than the base table.
Note: The functionality described above only works on enterprise and developer editions of Microsoft SQL Server 2005. In Microsoft Dynamics NAV 5.0 SP1 we didn't choose to use it as we did not want to limit Dynamics NAV implementations to the Enterprise Edition of Microsoft SQL Server.
Instead Dynamics NAV 5.0 SP1 will get data from the view explicitly like this: SELECT AccountNo, SUM$Amount FROM GLEntry$VSIFT1 WITH NOEXPAND WHERE AccountNo='1000' GROUP BY AccountNo
We split calcfields/calcsum calls per table and if no indexed view contains all fields then we will go to the base table.
We choose the indexed view based on a simple filter cost calculation and of course the SIFT index must be able to honor all the SumFields and all the filter fields:
Filter:"A=? AND B=? AND C>=? AND C<=?"
Keys:A,B,C,D - cost = 0+0+0+(1<<15-3) = 4096A,B,D,C - cost = 0+0+(1<<15-2)+0 = 8192A,B,C - cost = 0+0+0 = 0!D,A,B,C - cost = (1<<15-0)+0+0+0) = 32768A,D,E,B,C- cost = 0+(1<<15-1)+(1<<15-2)+0+0 = 24576
Martin Nielander (martinni)Program Manager
This post is the second part of the post NAS Tutorial which is based on KB article 861762, previously published on PartnerSource. This post is an updated version, and available to a wider audience (no PartnerSource login required).
This post describes how to use NAV Communication Component (Comcom) and NAV Bus adapter. Comcom is designed to enhance communication between Navision and other applications, like a Web server, a BizTalk server or some other external system. What kind of communication takes place, is all down to the specific application (like Commerce Portal or Commerce Gateway). In this document, we will only look at the mechanisms of sending and receiving a document.
This post also shows how to create a very small application that can send a message to MS Message queue, and receive this message again.
=== Updated on July 15th ===
I was made aware of a couple of important additional things to remember when working with MSMQ Busadapter:
1) Messages sent to NAS must have the label "Navision MSMQ-BA"
2) Text in the messages must be in the format UTF-8. You cannot use UNICODE.
There are more details about this here.
=== End of Update ========
What is Comcom and Bus adapter?Both Comcom and the bus adapter are automation servers developed by Microsoft for NAV. Comcom is a general communication component. All it does, is sending and receiving messages. You need to use a bus adapter with Comcom. The bus adapter is specific to the media used to transport the messages that Comcom sends and receive. At the moment there are two bus adapters: One for MS Message queue, and one for Named Pipes. In this document we will use the bus adapter for MS Message queue (MSMQ).
The reason for having Comcom as well as a bus adapter is, that you can create your application using Comcom without being concerned about what the transport media is going to be. When you decide on that / or if you want to change the media used, it will only require small changes – the application itself would need only very minor changes.
PrerequisitesNAV communication components come as part of SDK (Devkit) on the product CD. This needs to be installed before you start. And, you need to have MSMQ installed (installs as part of Windows).
First, create a new queue in MSMQ: Open Computer Management, expand "Services and Applications", and you should see "Message Queuing" here, if MSMQ is installed. Expand "Message Queuing", and then expand "Private Queues". Note that the MSMQ Busadapter is only tested with private queues, and is unlikely to work with public queues.
Rightclick on "Private Queues", select New -> Private Queue, and enter a name. Let's call it MuQueue. Also note that MSMQ Busadapter has not been tested for Transactional queues, so leave the field "Transactional" blank.
Now you have the queue, and the rest is up to NAV to send a message into this queue.
So, in NAV, create a new codeunit. First we need do declare the following variables to get access to Comcom:Name Type SubtypeComcom Automation 'Navision Communication Component version 2'.CommunicationComponentComOut Automation 'Navision Communication Component version 2'.OutMessageOutStr Outstream All automation variables need to be initialised before they can be used. Type these two lines to initialise Comcom and ComOut:
CREATE(Comcom);ComOut := Comcom.CreateoutMessage('Message queue://');
'Message queue://' is the actual word Message queue. We need to tell Comcom to send the message in a format for Microsoft Message queue. But this is the only reference to the media (until we get to the bus adapter).
Remember, Comcom has only one purpose: Sending / receiving messages. It is for writing the actual message that we want to send that we need OutStr. OutStr will contain the message, and Comcom will send it.
To associate Comcom with OutStr, add this line:
OutStr := ComOut.GetStream;
Then, compose the message using OutStr:OutStr.WRITE('This is my message.');
Once the message is done, we just need to send it. Send it in this way:ComOut.Send(0);
That’s the application done. Finally, we just need to add our bus adapter, which needs to be specific to what transport media we are using - in this case MSMQ.Add one more global variable:Name Type SubtypeMQBus Automation 'Navision MS-Message Queue Bus Adapter'.MSMQBusAdapter
To “plug in” the bus adapter, type in these lines at the beginning of the codeunit:
create(MQBus);MQBus.OpenWriteQueue('My-pc2\Myqueue',0,0);// On Win2000, use MQBus.OpenWriteQueue('.\private$\Myqueue',0,0);Comcom.AddBusAdapter(MQBus,0);
Your final codeunit should now look like this:
ComOut := Comcom.CreateoutMessage('Message queue://');OutStr := ComOut.GetStream;OutStr.WRITE('This is my message.');
To test is, try to run it, then look the queue and see if the message was sent.
Next, we want to receive this message.Receiving a message
Create a new codeunit with these variables:
Name Datatype SubTypeComcom Automation Navision Communication Component version 2'.CommunicationComponentInMsg Automation 'Navision Communication Component version 2'.InMessageInStr InStream MSMQBus Automation 'Navision MS-Message Queue Bus Adapter'.MSMQBusAdapterLine Text 250
Highlight the Comcom variable, go to properties, and set the property WithEvents to Yes. This will create a new trigger called:Comcom::MessageReceived(VAR InMessage : Automation "''.IDISPATCH")
This trigger will automatically run whenever there is a new message in the queue.
The finished codeunit should look like this:
Comcom::MessageReceived(VAR InMessage : Automation "''.IDISPATCH")InMsg := InMessage;InStr := InMsg.GetStream;InStr.READTEXT(Line);MESSAGE(Line);
Go to Codeunit Properties, and set the property SingleInstance to Yes. Now, you can either run it from the object designer, or you set up NAS to run this codeunit.
So you should now have 1 codeunit to send a message to MSMQ, and another codeunit which can listen to this queue, and which is triggered as soon as a message arrives.
Lars Lohndorf-Larsen (Lohndorf )
This post is based on KB article 861762 which was previously published on PartnerSource. This post is an updated version, and available to a wider audience (no PartnerSource login required).
It describes the basics of what Navision Application Server (NAS) does, and an example of how to build a NAS application in less than 30 minutes. The KB article continued with an example of how to use NAV Communication Components and BusAdapter. This part of the KB article is in the post Comcom and the Bus adapter.
What is NAS?NAS is a client without a user interface (GUI), running as a service. That’s all it is. When it starts, it will run a function in codeunit 1, passing on the Start-up parameter that has been set up. That’s all it does. There are two prerequisites for this function in codeunit 1:
It must have ID 99 (the name of the function is irrelevant) It must have one parameter Text 250. The name of this parameter is also irrelevant.
NAS on its own does not do anything else. So on its own it is a very simple component. But the application you build around it can, off course, be as complicated as any other application.
This tutorial will take you through the steps of building a simple application using NAS. After having completed the tutorial, I hope you will be comfortable with NAS, how to use it, and how to set it up.
1: Setting NAS
In this tutorial we will run NAS on a SQL Server. Just like with a normal client you can do the same things against a Native server. Just use Nas.exe instead of NasSQL.exe. I assume that you already have a NAV database running on a SQL Server, and that you can open this database with a normal NAV client.
Install NAS from the product CD.
Installing NAS will automatically create two services [Machinename-CLASSIC] and [Machinename-SQL] (display name is “Navision Application Server [Machinename]-CLASSIC/SQL”). Go to services and check that you can see these two services. These services are ready to use but just for the practise, the first thing we will do is to remove these two services and create our own service.
There are three interfaces you can use to administer NAS: Navision Application Server Manager (an MMC snapin) Windows Registry Command prompt
There are only two tasks that you can only achieve from the command prompt: Removing an instance (service) of NAS and adding an instance of NAS. All other tasks for administering NAS can be achieved using either of these three tools.
Remove the two services and add your own service:For this task, you need to use the command prompt. Go to the folder where you installed NAS. Notice that, in this folder you will find two .exe files: Nas.exe and Nassql.exe. In the following section, use nassql.exe since we are running on a SQL Server. If you were running NAV on Native server, you would use nas.exe. But each file has the same functionality.
Also note, that if you are running on Windows Vista, then opening a normal command prompt may not give you enough permissions. So, if running on Vista, open the command prompt from the Start Menu -> All Programs -> Accessories, then right-click on "Command Prompt", and select "Run as Administrator".
To remove a service, from the command prompt in the NAS folder, type: Nassql appservername=[machinename]-CLASSIC, uninstallasservice . This will remove the service with the name [machinename]-CLASSIC that was installed when you installed NAS.
In the same way, remove the [Machinename]-SQL service.
To add a service, type: Nassql appservername=NAS, installasservice
This will add a service called NAS. This is the service that we will use in this tutorial. You can add as many instances of NAS as you like – each one will appear as a service. If you want to run NAS from a different folder, then just copy the NAS files into that folder, and run the command prompt commands from there.
Set up the NAS service;Once you have an instance (service) of NAS, you will need to set it up. You can do this from the command prompt, but it is easier to user either the NAS manager, or Windows Registry. If you want to use Registry (Regedit), go to the key HKEY_LOCAL_MACHINE/SYSTEM/CurrentConotrolSet/Services and look for your service here. We will use the NAS Manager.
Remember from the previous step, that the name of your NAS is "NAS". But in Services, the display name will be "Application Server for Microsoft Dynamics NAS" if you are using version 5. Or "Navision Application Server NAS" if you are using version 4. In the next steps, use the name NAS.
To start NAS Manager, click Start, Programs, Microsoft Dynamics NAV, Navision Application Server Manager. Rightclick on "Application Server for Microsoft Dynamics NAV", and select New -> Application Server. Then type in the name of your NAS service ("NAS").
The parameters you need to set up are: Database Server Name: This is your SQL Server. Database: The NAV database. Company Name: Specify the name of the Navision company. NAS must be set up to run in a specific company. If you have more than one company, you need one NAS for each company. Start-up parameter: Specify any text string here. You can use this parameter to distinguish between multiple instances of NAS. For now, just specify “NAS”. Net type: Select Default. Object Cache: Specify Object Cache, or leave it as default.
Once this is done, click Apply these settings.
One more thing you have to do, is to associate your NAS service with a Windows account. Do this by going into services, right-click the NAS service and select Properties. On the “Log On” tab, under “Log on as:”, click This Account, and select an account. For now, you can just select the Administrator Account. In a real installation it is recommended that you create a new Windows Account and use that just for NAS. NAS can only use Windows Authentication. It cannot use database login.
NAS has now been set up.
2: Changes needed in NAV In NAV, create a Windows LoginIn the previous section you set up NAS to use a Windows Account. You need to give access in NAV for this account. Go to Tools, Security, Windows Logins and specify that account here. For this exercise, click Roles and give this account role ID SUPER. Make sure to synchronize permissions (Tools -> Secutiry -> Synchronize all logins).
Modify codeunit 1The last step is to create a function in codeunit 1. As mentioned before, NAS runs a function in codeunit 1 with the ID 99 and a parameter (text 250). In a standard NAV database, this function is called NASHandler. The code in this function depends on which version of NAV you are running, but in recent versions, it will contain a section that looks like this:
IF CGNASStartedinLoop = FALSE THEN CASE Parameter OF 'OSYNCH','JOBQUEUE': BEGIN IF NOT JobQueueSetup.GET THEN BEGIN JobQueueSetup.INIT; JobQueueSetup.INSERT; END;
This part of the code tells NAS to do, if it logs on with Startup Parameter OSYNCH or JOBQUEUE. So, add your own parameter here, like this:
IF CGNASStartedinLoop = FALSE THEN CASE Parameter OF 'NAS': BEGIN // This is your new section, and where you will tell your NAS what to do. END; 'OSYNCH','JOBQUEUE': BEGIN IF NOT JobQueueSetup.GET THEN BEGIN JobQueueSetup.INIT; JobQueueSetup.INSERT; END;
You now have your NAS application, but at the moment it’s not doing anything. Let’s make the simplest application in the world: Put one line in this new section: MESSAGE(‘NAS Has logged in!’);Close (and safe) the codeunit.
Test it!Now it’s time to see if everything has been set up correctly. Because NAS doesn’t have a user interface, it cannot open a message box. Instead, it will put any messages from the MESSAGE command as a message in the application log (Event Viewer). Check that NAS is working by following these steps:
1) Go to Services, and (re)start the NAS Service. Just because NAS starts, it doesn’t necessarily mean that it works!2) Open the Even Viewer (Start, Programs, Administrative tools, Even Viewer. Click “Application Log” and check the latest messages here. If you see a message saying ‘NAS Has logged in!’ it means that NAS has successfully run the function in codeunit 1.
Make an Application using the timer trigger:
Create a new codeunit. Declare one variable:
Name Datatype SuntypeNavisionTimer Automation 'Navision Timer 1.0'.Timer
Set the property WithEvents to Yes on this variable. Notice the new trigger that was created.
Add this code:OnRun()IF ISCLEAR(NavisionTimer) THEN CREATE(NavisionTimer);
NavisionTimer.Interval := 3000;NavisionTimer.Enabled := TRUE;
NavisionTimer::Timer(MilliSecounds : Integer)MESSAGE('Time is %1',TIME);
Run the codeunit and see that Nothing happens. – why is that?
Design the codeunit again, go to codeunit properties and set the property Singleinstance to Yes.Run the codeunit again and see the difference that this property made. SingleInstance means that the codeunit will keep running, waiting to be triggered by something - in this case, the timer.
The only way to stop a SingleInstance codeunit, is to close or reopen the company (Ctrl+O).
Now go back to codeunit 1, and replace the code in function 99 with this line:
Because of Object Cache, NAS may not pick this change up until you restart the service. So do that, and if everything works OK, you should now get the time in your application log every three seconds.
Remember that NAS is just a client without a user interface. So the simplest way to troubleshoot NAS, is to run whicever codeunit it runs from a normal NAV client. You can then better see what it is doing, and you can use the normal debugging features.
I just want to point you to the whitepaper describing the changes done for the Microsoft Dynamics NAV 5.0 SP1 SQL Option:
With the release of Microsoft Dynamics NAV 5.0 SP1, major changes have been made to Microsoft Dynamics NAV™ with Microsoft SQL Server. This document outlines these changes and shows how these changes can help you improve the Microsoft Dynamics NAV customer experience. This document will only cover changes to Microsoft Dynamics NAV with SQL Server.
This post is the first in a planned series to describe various "modern" methods for troubleshooting performance problems with Microsoft Dynamics NAV on SQL Server. The idea is also to make the best out of information that already exists, either in other places on this blog or anywhere else.
Modern Troubleshooting:The idea about "Modern Troubleshooting" is:
I want to make it clear that with "Troubleshooting", I mean "Trouble". And the methods I describe here will be quick-fixes for when something has gone wrong already. I don't mean to reduce the need for thorough performance- or scalability reviews, which can still require lots of work and understanding of the customers business. So I definitely don't want the "Modern Troubleshooting" to replace specialist consulting and ongoing (especially pro-active) performance tuning. Of course it is always better to avoid Trouble in the first place.
Identifying Trouble-queries:One of the most useful queries I have used for performance troubleshooting recently, is the "TOP 30"- query described here:Simple query to check the recent performance history
It returns the queries that are currently in SQL Server's plan cache, sorted with the queries causing most reads at the top. Every time SQL Server generates a query plan, then this plan is stored in this cache. And every time SQL Server (re)uses this plan it will also update some statistics about the plan, like execution_count, number of reads, etc. It is this information that the "TOP 30" query gives you.
SQL Server's plan cache is changing all the time, depending on what queries SQL Server runs, so you may get different results depending on what time of the day you run the query. The plan cache is also reset when SQL Server restarts, or you can reset the cache by running DBCC FREEPROCCACHE.
The result of the "TOP 30"-query is easy to copy into an Excel spreadsheet for further analysis, or to send to someone else. So it is simple data to collect, as long as the customer is on SQL2005 or later (Being based on Dynamic Management Views (DMW) which was introduced in SQL Server 2005, it not work for SQL Server 2000).
What to look at:After receiving the result of the query - preferably in an Excel spreadsheet - these are the things I look at:Being sorted by "max_logical_reads", you have the "worst" query at the top. But also look at execution_count. If a query ran just once, it might have been a batch job, or something else that is not really causing any problems. Of course, queries that have an execution_count in the 100s or 1.000s may be more relevant to look at. Also see if the queries (stetement_text) look similar, or if many of them look to be in the same area (same tables).
The column diff_quota shows max_logical_reads divided with min_logical_reads. If this number is high, it means that the query plan is inconsistent. This can be either because of inconsistent use of NAV (for example users applying different filters on the same table). Or, because a query plan is good for some queries but bad for others. In this case, some times you can affect the way that SQL Server creates query plans, either by adding RECOMPILE hints, plan guides or index hints. Or by upgrading to a newer version of NAV client (for example see the post "SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1" for how NAV 5 SP1 will cause different query plans).
If diff_quota is low, it means that the query plan is just consistently bad, which means that it is more likely that the query itself is bad. And, you will have to look for reasons why that query consistently causes the number of reads that it does. The "TOP 30"-query can't really help finding out why a query is causing many reads. But at least it can some times identify which queries to investigate first, which can be a very time consuming task otherwise (collecting and analysing profiler traces, etc).
You can also look at max_elapsed_time, but keep in mind that when a query takes a long time to run because it is being blocked, then the real problem is somewhere else (in the blocking query). So, if a query has a high max_elapsed_time, then see if the query contains a lock (WITH UPDLOCK). If it does, then you are most likely looking at a blocking problem which requires a wider look, and which often cannot be solved by the query you see.
So the "TOP 30"-query is always a good place to start. Some times it won't help you solving any problems, but then you will know that without spending much time. Other times, the query tells you right away which queries are causing problems, which can save you a lot of time. So I would always run this query first. And only if it doesn't help, then begin to look at collecting further information.
Lars Lohndorf-Larsen (Lohndorf)Escalation Engineer
When time passes by and people started to update their Office environments with the latest Office release, we immediately saw an increase of cases coming in that state that with Office 2007, E-mail logging refuse to log E-mails when using the Application Server while this worked in earlier releases of Office. This seems to relate to the way Office 2007, or better Outlook 2007, deals with security that is in the Trust Center. If there is no antivirus solution installed, then Office 2007 seems to act differently causing the whole confusion why E-mail logging does not work at once.
We at Microsoft do think that everyone now has an antivirus solution installed on their client PC's. Well I have news about that: not everyone does have a client with an antivirus solution installed. This is a misconception from our side. For example: in demo environments using virtualizations without Internet access, there is no absolute immediate need to install an antivirus solution. But we sometimes see PC's where this is indeed true: no antivirus solution installed or one that is installed but is actually expired.
In all those scenario's when using NAS, there are no error message and the QUEUE Public Folder is building up. As we all know already, when using NAS in combination with MAILLOG in NAV 4.0 or JOBQUEUE in NAV 5.0, we need to configure the Outlook Security Settings template in a Public Folder when using Exchange 2003. For later releases, GPO can do this for you, but that is out of scope of this blog.
Outlook 2007 can use either public folder security forms or Group Policy to manage security for attachments and for add-ins. The ability to use Group Policy object (GPO) settings to store security settings is a new feature in Outlook 2007. If your environment uses public folders, and if you use public folder security forms in earlier versions of Outlook, you can continue to use public folder security forms. You can do this after you make a minor change to the appropriate registry settings.
Outlook 2007 is designed to take advantage of the GPO settings to manage security for attachments and for add-ins. Unlike Microsoft Office Outlook 2003, Outlook 2007 does not use the CheckAdminSettings registry data to determine policy settings or to determine trust levels for add-ins. Instead, Outlook 2007 uses the new AdminSecurityMode registry entry to determine the security policy. The AdminSecurityMode registry entry uses the following configuration: Key: HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Outlook\Security DWORD value: AdminSecurityMode Values:
0: Use the default Outlook security settings Note This is the default setting if the AdminSecurityMode registry entry is not present. 1: Use the security policy from the Outlook Security Settings public folder 2: Use the security policy from the Outlook 10 Security Settings public folder 3: Use the security policy from the GPO settings Use the AdminSecurityMode registry entry to control the security settings that Outlook 2007 applies. You can configure Outlook 2007 to use the current security settings that are published through the existing Outlook public folder security forms. Alternatively, you can configure Outlook 2007 to use GPO-based security settings.In Office 2007 there is a now a so called Trust Center which you can find in the Tools menu of your Outlook 2007 client. If you click on Tools menu / Trust Center and then select the tab Programmatic Access, then you will see the following options: - Warn me about suspicious activity when my antivirus software is inactive or out-of-date (recommended) - Always warn me about suspicious activity - never warn me about suspicious activity
So, this means that if you do not have antivirus software on your Outlook 2007 machines, then you will get that security dialog box. It is recommended to make sure your antivirus solution is up to date. However, if that is not possible, then you can use a registry setting to override this setting: To deploy the configuration setting for programmatic access security, push out the following registry data Key: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Outlook\Security DWORD: ObjectModelGuard Possible Values: 0 (or missing) = "Warn me about suspicious activity when my antivirus software is inactive or out-of-date (recommended)" 1 = "Always warn me about suspicious activity" 2 = "Never warn me about suspicious activity (not recommended)
Marco Mels (mmels )Microsoft Dynamics NL
Microsoft Customer Service and Support (CSS) EMEA
It has come to my attention that in Outlook 2007 by default Contacts are not linked to Tasks. Let's assume you have a salesperson that goes onsite to a specific prospect and that he agrees to follow up. So he creates a Task in Outlook. In Outlook 2003, this was quite easy. You just went to the bottom of the Task window and you pressed on the Contacts button next to Categories.
This Contacts button is not there in Outlook 2007.
To enable this in Outlook 2007, you simply select Tools / Options / Preferences / Contact Options. Now you have the opportunity to links the contacts to every form. The salesperson is now able to create a Task while being onsite and link it to a specific Contact.
After synchronizing to NAV with the new Outlook Synchronization functionality, a ToDo is created for the salesperson with an additional contact added. The Contact should be there as well in NAV. That contact now also has a ToDo.
Marco Mels (mmels)Microsoft Dynamics NL
The command COUNT is pretty simple. But on the SQL option there are a number of different ways to count records, and each can give a different result. This is how the different ways of counting works in NAV on SQL:
There are two commands you can use: COUNT or COUNTAPPROX. As the name suggests, COUNTAPPROX is only designed to give an approximate count. But this does not necessarily mean that COUN is exact (I will come back to this later).
COUNTAPPROX:This command doesn't actually count anything. It only asks SQL Server to make a query plan for a SELECT statement. Then it reads the estimated number of records from the query plan. This is how COUNTAPPROX looks in a profiler trace:
It is the same as if you click on Query -> "Display Estimated Execution Plan (Ctrl+L)" in SQL Server Management Studio, instead of running a query normally (Ctrl+E / F5). This tells SQL Server to only compile a query plan, but not actually run the query. The query plan will contain "estimated number of rows", which in turn is based on SQL Server statistics. This is the number that COUNTAPPROX returns.
COUNT works differently, depending on whether there is a filter on the table or not. Without a filter it can just go to Table-Information, and get the number of records from there. This is quicker than going to the actual table and count every record. On the SQL Side, it means counting the number of rows from the sysindexes table, like this:
But sysindexes is not always up to date! Only just after having updated statistics, can you rely on the numbers in this table. So COUNT, without a filter, will not always give you the correct number.
If you have a filter, for example:
i := Cust.COUNT;
Then the method of just looking up table-information doesn't work. So NAV has to count the records the hard way. In this case we finally get an actual COUNT SQL command:
But even this method of counting is still not necessarily accurate, because it reads uncommitted data. If you run the following code from another client, and leave the CONFIRM-dialog:
OnRun()Cust.INSERT(TRUE);Cust."Currency Code" := 'EUR';Cust.MODIFY;
IF NOT CONFIRM('Continue?') THEN;
Then, the COUNT above will include a record which never existed / was never committed.
The only way to get an exact count, is by applying a lock. For example:
This will give you an exact count, whether you have a filter or not. On the SQL side it will look like this:
Because of the UPDLOCK it only counts committed records. And because of the lock, NAV knows not to just look up the number of records from table information, but to run a SELECT COUNT on SQL Server.
Lars Lohndorf-Larsen (Lohndorf)Microsoft Dynamics UK
This article describes the following procedures:• How to add a non-predefined report in Microsoft Dynamics NAV
• How to make the report available in Employee Portal in Microsoft Dynamics NAV
This article describes these procedures on a computer that is running Microsoft Windows SharePoint Services.
Typically, only some tables are configured for the demonstration site in Employee Portal. These tables include the following tables:• Customer
• Sales Header
The following reports are available to be added to Employee Portal:• Sales Quote (204)
• Order Confirmation (205)
• Quantity Explosion of BOM (99000753)
• Rolled-up Cost Shares (99000754)
• Detailed Calculation (99000756)
Note: This article uses the Service Item table (5940) to show you how to add the "Service Item - Resource Usage" report (5939) to Employee Portal.
How to add a non-predefined report:
To add the "Service Item - Resource Usage" report (5939) to Employee Portal by using the Service Item table (5940), follow these steps: 1. Add a report that is not related to the predefined tables. To do this, follow these steps: a. On the Tools menu, click Object Designer.
b. In Object Designer, click Codeunit, select codeunit 6828, and then click Design.
c. On the View menu, click C/AL Globals, and then add the new ServiceTableBased function.
d. Add the following new global variable: • Name: Service Item
• Data Type: Record
• Subtype: Service Item
e. Add the following line of code to the ServiceTableBased function.
ReturnValue := REPORT.SAVEASXML(ReportID,TempPath,TRUE,"Service Item");
f. Add the following line of code to the CreateReportPreview function.
REPORT::"Service Item - Resource Usage":
ReturnValue := ServiceTableBased(ReportID,TmpFileName,PrintRecordRef);
g. Click Save, and then click Compile.
How to make the report available in Employee Portal:
1. Add the Show Report action to the Action list of the Web part. To do this, follow these steps:a. Click Administration, expand Application Setup, expand Employee Portal, and then click Groups.
b. Select an existing "Web part request" card, and then click Card.
c. Click Table, and then click Card. Or, press SHIFT+F5.
d. Select the required table, and then click Actions.
e. In the EP WP Table Card dialog box, specify the following information:• Type: Show Report
• Target WP Request: Current
• Code: SHOWREPORT
• Target Table No.: 5940
• Report ID: 5939
• In the Caption field, type a new caption for the "Service Item - Resource Usage" report.
2. Add the Web part to Windows SharePoint Services. To do this, follow these steps:a. Open Employee Portal in a Web browser.
b. In the Add Web Parts list, drag a Web part to the location that you want in the window.
c. In the Modify My Web Part box, select the group that contains the Web part.
d. In the Web Part Request list, select the Web part for which you added the actions in step 1 earlier in this section.
Mohamad Vajid (mvajid)
Microsoft Dynamics ME
This article describes how to do the following:• How to configure a table in Microsoft Dynamics NAV 4.0.
• How to make the table available in Employee Portal in Microsoft Dynamics NAV.
This article describes how to do this on a computer that is running Microsoft Windows SharePoint Services.
Typically, Employee Portal has only some tables configured for the demonstration site. These tables include the following tables:• Customer
• Production BOM
Note: This article uses the Job (167) table to show you how to add a table to Employee Portal.
To configure a table in Microsoft Dynamics NAV and then make the table available in Employee Portal, follow these steps:Step 1: Create a new Web part request template card
1. Click Administration, expand Application Setup, expand Employee Portal, and then click Web Part Request Template.
2. Press F3. In the JOBCARD - EP WP Request Template Card dialog box, specify the following settings for the Job (167) table:a. In the Code box, type JOBCARD.
b. In the Description box, type Job Card.
c. Click to select the Editable check box.
d. In the Type list, click Card.
Note: The settings that you specify depend on the table that you are configuring. e. In the Caption field, type Job Card.
f. In the Table No. field, click the lookup arrow, and then click the Job (167) table.
g. In the Header or Line field, click Header.
h. If you want to populate the Web part with the first record from the table, click 1 in the First Record Shown field.
i. In the No. of Records field, click the number of records that you want to display in the Web part.
3. Click Table, and then click Card. Or, press SHIFT+F5.
4. In the JOBCARD - EP WP Table Card dialog box, follow these steps in the Code field for the General row: a. In the Header or Line field, click Header.
b. In the Caption list, click General.
5. Click Tab, and then click Card. Or, press SHIFT+F5.
6. In the JOBCARD 167 Header - EP WP Table Tab Card dialog box, follow these steps: a. Click the lookup arrow for the Field No field.
b. In the Length field, enter the length for each column.
c. In the Appearance field, click one of the following items for each column:• Visible
• Hidden or Editable
d. In the HTML Layout field, click the type of control to which each field belongs. For example, click Text Field, List, or Check Box.
e. Click to select the Required check box for the required field.
f. If you want to sort the table by a specific field, click to select the Sortable check box.
g. If you want to be able to populate a field by using information from another table, select that field in the table. Next, click the Field button, and then click Lookups. Then, click the table that you want to use to populate the field.
7. You can customize tabs such as the POSTING tab and the DURATION tab by following step 1 through step 6.You can now use the new Web part request template card together with existing groups. Or, you can create a new group. The new Web part request template card contains a table and its required columns.
Step 2: Add the Web part request template card to a group
1. Click Administration, expand Application Setup, expand Employee Portal, click Group, and then click SALES.
2. In the SALES - EP Group Card dialog box, click the newly created JOBCARD card in the list of Web part request templates.
Notes• Typically, you add the Web part request template card to a group so that the Web part request template card is available on the Web site.
• You can add the same card to multiple groups.
• You can move between groups by using the Next arrow or by using the Previous arrow on the toolbar.
Step 3: Assign the group to a user
A user who has access to the Sales group will also have access to the JOBCARD card that is created and that is added to the group. Otherwise, you must create a new user.
Step 4: Add the Web part to Windows SharePoint Services
1. Open Employee Portal in a Web browser.
2. In the Add Web Parts list, drag the Card Web Part Web part to the location that you want in the window.
3. In the Modify My Web Part box, click Sales in the Group list.
4. In the Web Part Request list, click Job Card, and then click OK. Then, review the Job Card dialog box to make sure that all the fields and all the tabs that you configured are displayed.
Mohamad Vajid (mvajid)Microsoft Dynamics MEMicrosoft Customer Service and Support (CSS) EMEA