Tip #10 – Drop Down List

This Blog Article was brought forward from my old blog

Following the blog of Claus about drop down lists I would like to add some follow which is new for most developers.

Claus’ blog give a very accurate description about how to work with this new cool feature of NAV2009 but when I was explaining it for a developers class I found a little mistery.

The blog describes that, when you do not specify it, NAV will show the primairy key. But how does that explain the drop down of the Currency…

Please note that it shows Code and Description…

And nothing is defined in the Field Groups.

But the PK is just Code.

The explanation is simple. NAV will automaticaly add the Description field to the dropdownlist. Cool huh!

Same seems to work for the Name field.

Good luck

Tip #9 – Gain access to a database without a password

This Blog Article was brought forward from my old blog

Update 2014 : For the latest version of Dynamics NAV, this still works but you need other tables.

This is often an issue when you get a SQL Backup from a customer to analyse.

Off course you can create a user on SQL and change the password, but it is easier.

Navision stores the users into two tables. User and ‘Windows Login’, The first table is for database logins, the latter for Windows Logins.

When you delete all records from both tables Navision will no longer ask for passwords and allowes blank access.

So to all end users: Protect your backupfiles! Stick out tongue

Tip #8 – SQL Server Script button

This Blog Article was brought forward from my old blog

This tip is about the SQL Server Management studio again.

Many Dynamics Nav customers and consultants are very new to SQL and the programming language T-SQL.

The SQL Server has solved that with introducting tons of wizards for common tasks like backing up databases and creating users.

Let’s look at one of those (Backup);

With this wizard we can easily create a full, incremental or transaction log backup of the database.

Without going in the details about this wizard I would like to point out the script button

This button exists on a lot of SQL Server wizards. When you push it you get this in your query window

You can use this to either study T-SQL or create your own scripts to reuse or schedule!

Tip #7 – SQL Range Locks

This Blog Article was brought forward from my old blog

UPDATE 2014 : This no longer applies to the latest versions of Dynamics NAV (2013 and up)

In my previous tip (http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/13/tip-6-find-locked-records.aspx) I showed you how to find which exact records are locked and ‘proved’ row locking with the ‘LOCKTABLE’ command.

But even then you can eperience another fact. Sometimes SQL ‘locks’ records that are not yet created. This can be very irritating, especialy when for example several users are creating sales orders in batches.

What causes this to happen?

The answer can be found in the serveral locking types SQL server knows. The easiest one is the record lock. This lock is what you get when you ‘GET’ a single record when fitering on the unique value or ‘primairy key’ in Navision language.

To check this we will write the following code

.

(Don’t you love the colors Stick out tongue)

When we run the codeunit, a Sales Line is inserted. Please notice you need the .GET to actualy insert the records because of the buffered inserts introduced in SP1 of 5.0

When we open a second client on the same SQL Server database and open the Sales line table we see the record

This is because SQL Server does not know the Version Principle that the Classic or ‘Native’ database knew. We can actualy see a record that is not yet committed into the database. COOL.

Now let’s check the locks in SQL Server. We do this using the sp_lock command.

I will not go into details about lock types but you can read some here. http://msdn.microsoft.com/en-us/library/ms191272.aspx

All that is interesting now is that we can insert a neighbouring record using a second client.

To do this we copy the codeunit and change the numbers.

After executing we see this in a ‘Third’ client.

And this in the SQL Management Studio

So lesson learned: We can create two neigbouring records in the sales line with two transactions from two clients. No blocking, no deadlocks, nothing.

Perfect. So how come I have (dead)(b)locks in my database.

Let’s release both locks by hitting Ja. (Dutch for Yes Stick out tongue)

And write a new codeunit.

This reads all saleslines from the ‘Mark’ document without a cursor.

Now when we issue sp_lock we get this

Note that we get ‘RangeS-U’ types of locking.

Now let us try to create line number 30000.

When running this codeunit we get this error

THIS IS WHY YOU GET BLOCKS AND DEADLOCKS.

Navision is full of this kind of statements.

So how do I avoid this.

First lesson is to avoid the validate statement when populating records. This is a very nasty habbit of a lot of developers. Have a look at standard NAV code. How many validates are there in Codeunit 80? And 12?

Another trick can be to read into a temp table first and ‘get’ the records one by one.

Good luck with this one. Big Smile

Send me an email when you want the objects. Idea

Tip #6 – Find locked records

This Blog Article was brought forward from my old blog

Sometimes, when you investigate performance issues you see users blocking each other on small tables with good indexes and small locks.

In that case it can be very interesting to know exactly which record in the table is locked and which records are available.

That is what this tip is about.

Most hardcore NAV developers are familiar with the statement “IF CODEUNIT.RUN THEN…” and most of you might also know that NAV supports Lock timeout.

These principles are the background for this tip.

To be able to implement this you need at least two codeunits available in your license.

The first codeunit has a table parameter. It is the table you want to investigate.

It should contain this code

The second codeunit is a normal codeunit with a bit more code

And these variables

For this to work, we need to change the settings of the database under file -> database -> alter

Change the Timeout Duration to 0 seconds. If you do this in a production system, then temporary turn of the Lock Timout option.

Now let’s lock some records in the table.

And see here the result:

Hopefully this wil also eliminate to of the oldest myths in the Navision on SQL Server world; locking by neighboring and table locking by the LOCKTABLE command.

BTW: You can also make this smarter by implementing this code:

In a SingleInstance codeunit off course.

Good luck Stick out tongue

Tip #5 – Multiple country databases; Validate Codepage

This Blog Article was brought forward from my old blog

As you might be aware of, the NAV client is not Unicode compliant.

[Update 2014, since this was posted in 2009, NAV is Unicode compliant, however Codepage is still used for sorting ]

Usually this is not a big issue since NAV is orriginaly used in countries that do not require double byte characters and generaly NAV installations are single country, so one codepage is enough.

I will blog more about codepages in the furure but for now I want to focus on the option to activate multiple codepages.

When you start up the NAV client it checks if the codepage on your windows system matches the STX file. Also when you create a new database it will only let you pick a collation in your region.

To override this, you can go to the settings of the SQL database in File -> Database -> Alter.

If you uncheck the “validate codepage” flag the check is no longer performed.

Be carefull because if a codepage contains a character that is not supported in the other codepage, you might see rubbish.

As long as this is not a problem for your organisation than it is ok.

Tip #4 – Tuning VSift

This Blog Article was brought forward from my old blog

After all the theory about VSift in my previous blog posts now a tip about how to tune VSIFT.

Before I continue first this.

VSift is not a bad technology but the implementation in NAV has one big downside. The good old SIFT levels have disapeared.

In older versions of NAV there was a SIFT Level property. This enabled you to decide which SIFT level best suited your implementation.

Many people say: Disable all top levels. But I disagree on that. Measure the exact levels you need and only enable those or better: creating your own levels.

That is what this blog is all about: creating your own levels. Before SQL came I used to tune big C/Side databases and also then we did not have this SIFT Levels property, so we had to create our own.

To know how that is done it is critical to know how the NDBCS driver works. This driver decides which VSIFT to use. This driver is as stupid as SQL is intelligent. (Sorry Dean). As far as I know it has always been this way, also in the Natvive days but maybe some MSFT folks can post about this.

When NAV need to calculate a flowfield it reads the Key table top down and grabs the first key that matches the definition.

Now since selectivity and number of reads/records is extremely important for your performance it can be interesting to create keys that are narrower and reduce the number of reads.

Example:

When I open the G/L account in NAV this query is executed:

SELECT

SUM(“SUM$Amount”) FROM dbo.”CRONUS Nederland BV$G_L Entry$VSIFT$1″ WITH(NOEXPAND) WHERE ((“G_L Account No_”=@P1

))

With an average of xx reads.

Now when I disable this VSIFT$1 level, NAV has to go to the next available level.

When the other fields in the index are heavily used and have a lot of values, the number of reads can be huge!

So what if you have a customer who uses NAV for 7 years thus having potentialy 2500 posting dates.

In the old days you would simply enable the top SIFT bucket but what with VSIFT?

Answer; Create a new key above the old keys so the driver will pick that up and give it priority.

Voila, it is as simple as that.

BUT: Be carefull: You could potentialy be changing business logic. NAV has the nasty habbit of allowing halfsyntaxed SETCURRENTKEY statements. In that case it uses the first key that it can find that starts with those fields. Maybe that in another blog… Stick out tongue

 

Tip #3 – Filter in SQL Management Studio

This Blog Article was brought forward from my old blog

This time another tip on how to use the SQL Management Studio.

Dynamics NAV (Navision) keeps a copy of all tables on SQL server for each company you create, no matter if the table is populated or not. This way of creating tables can lead to a long list of tables in the SQL Management Studio.

Expanding the table tree can even take minutes on heavy used systems. How to avoid that.

Step 1. Go to the SQL Management Studio, connect to the server and expand the database you want to use

Step 2, right click on the table or view secion and choose filter.

Step 3 – Apply the filter

Remember that it is automaticaly a part of the filter, not an exact match

And Voila

Tip #2 – Linked subpages

This Blog Article was brought forward from my old blog

Last months I have been teaching a lot of NAV2009 upgrade workshops in The Netherlands, Belgium and the UK. One of the questions I am getting often is how to link a subpage to another subpage. In the UK workshop we started some experimenting. We found that when creating a subpage you get a field called provider ID in the properties.

This field is used in factboxes to link a factbox to the subpages, for example Item statistics to a salesline.

When you populate this field with the ID of the subpage you want to link it to you can populate the subformlink with the link to the correct table.

Example:

Page 42, Sales Order has a part called Sales Line FactBox. The properties are like this

I created a new page with ID 50000 which is a list page of table Document Dimension

Very simple page. And in page 42 I created a new subpage under Saleslines

.

Normaly a subpage is linked to the main page, but when using the provider ID you can link it to another subpage

Voila

And now the sales order page looks like this

and it updates when selecting another salesline. Very cool huh? Stick out tongue

Tip #1 :SQL Management Studio – Include Column Headers

This Blog Article was brought forward from my old blog

Time for some tips about how to use the SQL Server Management Studio. This is a really great tool which should be more used by NAV DBA’s.

First tip is how to include column headers when copying query results. This is something we often use when analysing systems remotely.

When opening the management studio goto – tools – options

 

Then select – Query Results – SQL Server – Results to Grid

And select “Include column headers when copying or saving the results”.

After this, the column headers from the query results are copied to the clipboard and pasted into MS Excel.

Voila