Where are my keys?

Ever were searching for your keys? A colleague and me were searching for them once.

BTW: I am not talking about our car-keys or office-keys but about our NAV-keys!

And we found out that when a table is locked in NAV2013 (I didn’t check out NAV 2009R2 and NAV2013R2) by someone. If someone opens the table and wants to check the keys, they aren’t there.

NAV integer table in SQL-statement - improved

Maybe you remember my old post NAV integer table in SQL-statement.

It does work but has some drawbacks: it works well for small tables but get very fast very slow with a greater number of rows.

There are a lot of possible methods that are better. The best (most of the time) is the Itzik-Style Cross-Join.

  E1(N) AS (
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
  E8(N) AS (SELECT 1 FROM E4 a, E4 b)  -- 1*10^8 or 100,000,000 rows
For some other possible ways, I’ll point to the article I read about it.
Hidden RBAR: Counting with Recursive CTE’s

I forgot my administrator password

Ever had this problem? I just did in a virtual machine……

But I got it back using this link : http://pogostick.net/~pnh/ntpasswd/

I hope this is NOT helpful to you because you never need it. In the other case, I really hope it is helpful to you!

SQL Server 2014 and NAV

SQL Server 2014 has gone RTM on the first of April 2014.

For the moment Microsoft NAV has not yet confirmed SQL 2014 as compatible with some NAV versions. I hope they don’t wait too long.

But does it has some goodies that are useful for NAV?

Lets see the top features of SQL Server 2014:

  • Enhanced memory support: Standard Edition supports up to 128GB of memory. For the moment I haven’t even 1 customer that has 64 GB of memory for SQL server (the limit for SQL 2012 Standard Edition). But it might be useful in very large NAV installations.
  • Encrypted backups: I haven’t had a customer that wanted encrypted backups. But it IS Standard Edition!
  • Backup to Azure: Standard Edition : Well this could be interesting. Best practice is to have your backup not on the same physical server (and even less on the same physical disks…) and not even in the same room and even better : not even in the same building. Best would be on another continent (you never now if some meteorite will hit). Well, backup to Azure is the best you can have. In the Microsoft datacenter, you have 3 copies on different servers and if you have GEO redundant storage, you also have a copy in another Microsoft datacenter. Well, Encrypted backups as Standard Edition feature DO have sense now!
  • Delayed durability: Express Edition!: Normally when you COMMIT some data, SQL writes the data in the logfile and then your session is freed up again so you can continue working. Delayed durability keeps the COMMITed data in memory to harden it (=write it to disk) later. Does this ring a bell? The Native NAV database has had this for years! Of course both have the same problem: you COMMITed the data and it hasn’t been written to disk and your server crashes => you lose that data. This feature could be very useful if your transactionlog has performance problems and you can affort some dataloss. For more info : http://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx
  • Buffer Pool Extention (only on 64 bit): Standard Edition. If that 128 GB of memory is not enough, and you don’t want Enterprise Edition, you can do something in-between: put an SSD in your server and tell SQL to use it as extra memory with speeds somewhere in between memory-speed and disk-speed. Useful if your DB-size is expressed in TB instead of GB, but not really for NAV.
  • Enhanced Columnstore indexes: Enterprise Edition. Instead of indexing some fields in a table, you index only 1 column. So this index can be compressed very well and be scanned very fast. Very good for DWH. In SQL 2012 they existed but you had to disable the index before you could update the underlying table. Now you don’t need to disable the index anymore. Not really useful for NAV being an ERP and not a DWH. And there was no support for it directly in NAV (and probably it will never be supported). You would need to create/maintain them using TSQL.
  • In-memory tables (the feature is called In-Memory OLTP): Enterprise Edition. OLTP you say? Now it is getting interesting. NAV is OLTP! What are in-memory tables? An in-memory table (and its indexes) is kept in memory instead of on disk. Isn’t that the same as pinning (http://technet.microsoft.com/en-us/library/aa258284(v=SQL.80).aspx) a table? No, because pinning still using the same procedures for locks and latches. In-memory tables do not use those. They have their own procedures that are optimized for memory. Don’t worry, the data is still hardened to disk (don’t forget delayed durability), so your data is safe. They do have some limits like no AutoIncrement properties (=IDENTITY in SQL), clustered index is mandatory (is that a limit?), … Indexes are NOT maintained on disk, but only in memory, so when you start your server, it might take somewhat longer because SQL has to create the indexes on-the-fly in memory. Backups are faster,because the indexes are NOT backuped. Very important is that you need to have the memory to keep all that data in memory and keep some memory for the other tables/locking/… . This is a feature that has its uses in NAV, but NAV should support them natively (=using table-properties and NOT directly in SQL!) but it is still an enterprise edition feature. So it is not widely usable in NAV. But still a feature for which I see some cases where I might use it!

To finish, a link to the page where you have all the SQL 2014 features and in which edition you can use them : http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

NAV Techdays 2014

Get ready for NAV Techdays 2014 : 20+21 November 2014 + the pre-conference day on 19 November 2014!

Where is the bug?




Do you find the bug?

Yes? Good. Actually, there are 3 bugs!

1) The bug you found: the developer creates the file (if it doesn’t exist) and then opens it.

2) The Classic client does NOT generated an error in this case

3) The service tier does NOT behave as the classic client but just exits without an error.

This is on NAV2009R2. I didn’t check NAV2013.

NAV 2013 C/AL editor can contain up to 240 chars on a line

Remember how many times you were typing code on a line and hit the invisible wall of 130 chars?

A lot of times, I suppose. At least I have.

Well, with NAV2013 you can get up to 240 chars!

Definitely useful if you copy a function with all its parameters in it to your code where you want to use that function.

But for the rest I would advice anyway to limit yourself to 130 chars.

Why? Because not everyone has a very-wide screen for starters and also because it is just too wide to have a good overview of the code.

And in case you write some code that you want to downgrade to older versions, you better limit yourself to those 130 chars!

Variable-names in NAV2013 can be longer than 30 chars

Before NAV2013, the names of variables (and functions) were limited to 30 chars. In NAV2013, you can go beyond that length.

I did some tests to see if NAV only considers the first 30 chars or also considers the chars after that length. NAV2013 considers also chars beyond the first 30!

I didn’t check out how long the variables can be because I think that 30 chars is long enough.

And in case you write some code that you want to downgrade to older versions, you better limit yourself to those 30 chars!


I noticed some strange behaviour of NAV2009R2 with QUERYREPLACE (of course with File=>Database=Alter=>tab Options=>Enable for Microsoft Dynamics NAV Server=TRUE):


=> gives a warning "Function ‘QUERYREPLACE’ is obsolete for Microsoft Dynamics NAV Server.". Nice! I don’t want to have an error when running in the NAV-servicetier, so I will fix my code.



=> does NOT give a warning. So the NAV-compiler is intelligent and knows I will never use the QUERYREPLACE in the servicetier thanks to this IF-statement.



=> gives a warning. I will never use the QUERYREPLACE in the servicetier but the NAV compiler didn’t figure that out!

I admit this blog post will not help you in your development, but it is a nice fun to know feature.

I created a new page, put some fields on it but they don’t appear in the Windows client

I also restarted the service tier, but still the same problem. And the preview shows the new fields!


Probably in your personalized settings, there is some leftover that hides the fields.


Go to "Customize this page" and where you see "Restore defaults", click it (there are multiple ones and I didn’t check out which one exactly does the trick). After that you should be able to see the new fields.

Create a new blog and join in the fun!