Waldo's Blog
Microsoft Dynamics NAV

The Belgian Dynamics Community

Since a few months now, we have been working on a new initiative in Belgium: the Belgian Dynamics Community.

Now, who is "we", and why would we need another "community" while there is mibuso, dynamicsuser, numerous blogs (sustained engineering, waldo , Team Blog, ....), ... .

We

We are called the "board members", but I would just like to call it "the people who started off" . Here is an overview:

Patrick Dalle

Former marketing manager at Navision Belgium
10 years of experience with Microsoft Dynamics
Managing director of Lemarco

Kurt Juvyns

12 years of experience with Microsoft Dynamics
Microsoft employee 

Ludo Van den Ende

Former product manager at Navision Belgium
12 years of experience with Microsoft Dynamics
Helios-IT employee 

Luc Van Dyck

Founder and administrator of the online community Mibuso
11 years of experience with Microsoft Dynamics
GMI Group employee 

Eric Wauters

Administrator of www.waldo.be
7 years of experience with Microsoft Dynamics
Partner at iFacto Business Solutions 

Rodrigue Dion

Project leader of MATEXI
8 years of experience with Microsoft Dynamics
Working for end customer

As you can see, all very experience people ... In fact, I've got less experience than any one of them . Hm ... I should think this over ... . (And the word you're looking for is ...) ... ANYWAY ... These are not only experienced people, but also one by one "community animals". And that's only positive for what we're trying to do, isn't it? So...

Why another community?

Very simple. We're not trying to compete with other online communities. In fact, we're trying to make a difference with the other communities:

• We're trying to reach not only technical people, but also more functional consultants and end users;
• The online community is actually a small part af the initiative: we will organise evenings for information sharing, networking, ... as well;
• Futhermore we'll try to bring microsoft closer to the end user and vica versa, by organising round tables and such;
• ...

That's all .

To conclude, I would like to take this opportunity to announce our first event :the Belgian Dynamics Community Launch event on the 24th of April 2008.

The agenda on this event is not completely final but we can already announce that the Microsoft Dynamics NAV development team from Denmark decided not to miss this unique opportunity.

They confirmed to be present and show us upcoming technology in some sneak preview from the Microsoft Dynamics product family.

Platform updates overview - 3.70.B - 5.0 SP1

Here is the next I-think-up-to-date version of the Service Packs/Updates/hotfixes/Knowledge Base Articles/... Post. Enjoy!

Here is a list of knowledge base articles that were added in januari 2008 for Microsoft Dynamics NAV.

Microsoft Dynamics NAV 5.0 SP1

I know there is no download on the 5.0 SP1 yet today, but the URL already exists, so you can check once in a while. This release is really going to be quite "innovative" because of the more than 300 improvements in the application and the platform. Amongst them:

• Introduction of Bulk Inserts and SIFT with Indexed Views.
• Dynamics Mobile will be part of 5.0 SP1.
• Also, NAV 5.0 SP1 will be available in all localisations.

A few remarks:

• Because of the amount of hotfixes that comes out every month, It is quite difficult to keep above table up-to-date. That's why I will try to put only the client updates (new fin(sql).exe, nas(sql).exe, ...) above with the corresponding knowledge base articles. Furthermore, I will always add the links I get to the KB articles that were added in the current month (see link above). Natalie tends to help me on this .
• I changed the numbering. A major update (like update 1, update 2, ...) is actually a platform update. Usually these are cumulative. A minor update (like update 1.1, update 6.3, ...) are just KB articles which contain a new fin.exe, but is not cumulative. Usually the platform update is still needed to install.
• The updates in italic are the ones I added since last time.

Any comments are always appreciated .

SQL Server: sa locked out automatically

I had this issue at a customer of ours. The error message "Login failed for user 'sa' because the account is currently locked out" surprised me a bit. I didn't know this was possible. Actually, it is even possible to not have any admin anymore in the database, by locking out sa.

Luckily there are solutions that I would like to share with you.

You have this behaviour when you set the setting on your sa account "Enforce password policy" (Security/Logins/sa/Properties). If you uncheck this property, the sa account won't be able to lock anymore.

Now what do you do when the sa account is locked? You should run the following T-SQL Statement:

ALTER LOGIN sa WITH PASSWORD = 'yourpassword' UNLOCK

Like I said, this way, it is perfectly possible to not have a user with admin rights in your database: if you only activated SQL Server authentication. With a simple hack, you can change this as well. Just change the registery key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now, you should be able to log in with a local administrator account and unlock the sa account like mentioned above.

Hope this was useful .

Sources: MSDN / Urig

What impact does my C/AL have on SQL?

This is a very challenging blogpost in my opinion. I see many stories going around on what would be the best way to use the new FIND-instructions, what C/AL to write in what case. I have my own thoughts on this which I would like to share. I don't want to say the following is the best way to go, let's just say it's a good way to go .

First question (and I think the most important question that is on people's mind), how to use the FIND statements. This is not easy, because using the FIND statement really depends on the type of loop you're using.

First of all the basics. I think the table from stryk explains it very well:

A ground rule might be: try to fetch as less data as needed - but all data you need - in one server call. E.g. Don't fetch all records if you're not going to loop those records / Don't fetch data if you just want to know if a record is present for that range or not, ... .

The table above is nice, but it still doesn't make me think about the things I have to take in count when writing code. Let me try to help you with this by asking a few basic questions:

WHEN NOT LOOPING DATA

• Am I going to change data?

  Suppose you write this code:

  recCust.GET('10000');
  recCust.VALIDATE(Name, 'Van Terp Kantoorinrichting');
  recCust.MODIFY(TRUE);

  Not really exciting. In fact, I see code like this going around quite often. Well, the problem with this is that you do an extra server call, because you didn't apply that you wanted to change data. In fact, you'll see this in the client monitor:

Two times the SELECT is overkill, and must be avoided. And avoiding it is simple. Specifying that you want to change the data can be done by "LOCKTABLE". This makes our small piece of code like:

recCust.LOCKTABLE;
recCust.GET('10000');
recCust.VALIDATE(Name, 'Van Terp Kantoorinrichting');
recCust.MODIFY(TRUE);

To make the comparison complete, here is what happens behind the scenes:

This being said, take a look at following example:

IF ICHandledInboxTransaction.FIND('-') THEN BEGIN
  ICHandledInboxTransaction.LOCKTABLE;
  ICHandledInboxTransaction.Status := ICHandledInboxTransaction.Status::Posted;
  ICHandledInboxTransaction.MODIFY;
END;

This is code from default NAV (earlier version) which could be replaced by (to not change the behaviour on Native):

IF RECORDLEVELLOCKING THEN
  ICHandledInboxTransaction.LOCKTABLE;
IF ICHandledInboxTransaction.FINDFIRST THEN BEGIN
  ICHandledInboxTransaction.LOCKTABLE;
  ICHandledInboxTransaction.Status := ICHandledInboxTransaction.Status::Posted;
  ICHandledInboxTransaction.MODIFY;
END;

The statement "RECORDLEVELLOCKING" is the way for checking in code whether you're working on SQL Server (TRUE) or Native (FALSE). Furthermore, I added these two lines of code because it's necessary to lock before we read (to send the UPDLOCK in SQL).

• Does a record exist?

  There are two (good) ways to check whether a record is present for a certain range:

  • Using FINDFIRST: Use this statement when you need the data of the first record, and the first record only.
  • Using ISEMPTY: this is a somewhat forgotten statement, but it's lighter then the FINDFIRST. If you only want to check if a record exists in the filtered range, but you don't need any value of a field of any of the records, use this statement.

  See table above for what SQL Statement it produces (TOP 1 * or TOP 1 NULL).

  For example:

  IF SalesLine.FIND('-') THEN
    Cust.CheckBlockedCustOnDocs(Cust,"Document Type",TRUE,TRUE);

  Could be replaced by

  IF NOT SalesLine.ISEMPTY THEN
    Cust.CheckBlockedCustOnDocs(Cust,"Document Type",TRUE,TRUE);

WHEN LOOPING DATA

That wasn't that exciting, was it? But what if we want to loop data. That's a whole different story .

First thing that I have to state is that NAV uses cursors to simulate native behaviour. The new FIND statements (from 4.0) are the first step to go around this native behaviour.

Looping without changing data

In Stryk's tabel above, you can see the difference on SQL level between FIND('-'), FINDFIRST and FINDSET. I'll try to make the comparison of what happens on SQL Server:

What do we learn from this:

• The best way to go is using the FINDSET. Why? It's fetching a SET of records and will be working with this set all the way. You see that there is no cursor activity (no FETCH statements). If you read further, you'll see that this is not always the case!
• FINDFIRST is worse then FIND('-') when looping records because of the extra call (TOP 1 *). NAV does not expect a loop after a FINDFIRST. If it sees that you are looping, it's doing a new SELECT * (same as FIND('-') statement) to be ready for the loop and working with cursors to loop through the data (therefore the FETCH statements). Therefore, please do not just replace FIND('-') with FINDFIRST.
• Never blindly replace FIND('-') by FINDFIRST. Think of what you're doing.

Now, what about the number of records? Does that count?

Well, NAV behaves like this: if the set is bigger then 500 records (or the size you specified as "Record Set" value in the "Alter Database" window), it will not use the recordset, but is going to "cursor" it's way through the rest of the loop, like "old school". I'll try to show it using tho examples. In both of them, the code runs 2840 times through the loop (or in other words, the messages shows "2840").

In case of the FINDSET: A first impression shows you the when the resultset is bigger than 500 records, it just throws an extra database call without the "TOP 500" and is going to cursor it's way through the records. When you examine deeper, you conclude that it's actually looping through the recordset of 500 records, and after that, NAV does not call the next 500 records, but just trows a "SELECT *" and is going to cursor it's way through the rest of the records.

That's why with FINDSET, we have about 500 FETCHes less than FIND('-'). BUT, we have an extra database call, which is quite "heavy" (a "SELECT *" is heavier than a "FETCH 60").

It's hard to discuss what's best. Further benchmarking should point that out. I would bet my money on using FINDSET in all cases. Why?

• You avoid about 10 times a FETCH x call
• A "SELECT TOP 500" as extra database call doesn't seem that heavy for me.

If anyone is interested in doing a thorough investigation on this ... Please count me in or let me know the results.

What do we learn from this:

• Using FINDSET in front of every loop, is a pretty sure bet.
• Using FINDSET only gets the first 500 records as a set, but is going to cursor it's way through the rest of the records like FIND('-').

Looping with changing data

The same rules apply like above, only see if you're locking the data before you're reading it. You can do this by either using LOCKTABLE, like:

recGL.LOCKTABLE;
IF recGL.FINDSET THEN
REPEAT
...
UNTIL recGL.NEXT = 0;

Or, if using the FINDSET statement, you can use its parameter like:

IF recGL.FINDSET(TRUE) THEN
REPEAT
...
UNTIL recGL.NEXT = 0;

There is also an option FINDSET(TRUE, TRUE). Only use this if you are going to modify any field value within the current key. Now, I really don't recommend to use this. You have to be aware for the "NEXT FROM HELL" (like Hynek likes to call it).

The "Next from hell" can happen when you filter on a value, and in a loop, you're changing that value. The loop jumps to places that are hard to predict. Also, when you change a key value in a loop (even without modify), a "NEXT" can give unexpected results. Try to avoid these situations by just declaring a new variable, and modify in that variable. For example, this is looking for trouble:

recCust.SETRANGE("Country/Region Code", 'BE');
IF recCust.FINDSET(TRUE) THEN
REPEAT
  recCust.VALIDATE("Country/Region Code", 'US');
  recCust.MODIFY;
UNTIL recCust.NEXT = 0;

You're modifying the Country Code while you're filtered on that field. Try to use a seperate record variable, and leave your looping variable intact, like:

recCust.SETRANGE("Country/Region Code", 'BE');
IF recCust.FINDSET(TRUE) THEN
REPEAT
  recCust2 := recCust;
  recCust2.VALIDATE("Country/Region Code", 'US');
  recCust2.MODIFY;
UNTIL recCust.NEXT = 0;

Conclusion

When I started writing this article, I didn't expect that it was so hard to put this subject into words. I hope the above is clear to you, but to be sure, I just wanted to give you an overview of the things mentioned above.

• Not looping data
  • Modifying data
    • Use LOCKTABLE with GET or FINDFIRST
  • Not modifying data (usually to check if a record exists or not)
    • Use FINDFIRST if you need the data of the record further down the road
    • Use ISEMPTY if you do not need the values of the keys
• Looping data
  • Not modifying data
    • Use loop like: IF FINDSET THEN REPEAT UNTIL NEXT=0;
  • Modifying data: see that you lock the records that you read.
    • Use loop like: IF FINDSET(TRUE) THEN REPEAT UNTIL NEXT=0;
  • Mind the "NEXT FROM HELL" !

So, always think what the code is doing, then make up your mind what you will need for most optimal performance on SQL Server.    

Writing this article, I used the SQL Profiler and Client monitor to monitor what my code is doing. I noticed very few people know what this client monitor can do, so who knows, there might be an upcoming blog about monitoring your code .

Any comments are appreciated!