Dealing with Consistency Errors

NAV's consistency function is seldom used but very valuable when you need it.  Simply put, it ensures logical integrity at the transaction level.  This means that when a transaction is committed, it has followed any business logic that you wish to enforce.   That last part is important.  It is up to the developer to determine which conditions make a transaction consistent and then set the consistency flag at an appropriate spot in the code.  The use of this function does have one major drawback: it is anywhere from difficult to impossible to track down the source of a consistency error using the debugger.  Because the source of a consistency error depends on what may or may not have happened during the course of the transaction, the best way to figure out what went wrong is to look at the data that got generated at the state it was in right before the commit.

If only there was a way to do this...

Step 1: Create a debugging instance.

First, create a new service instance unless you have one that you're not using.  You only need this instance for the debugging session, so once you're done with it, you can stop or delete it.  The thing that will make this instance special is that you will disable buffered insertion.  The only other thing you need to ensure is that you have access via client services so you can test and debug under that session.

Step 2: Start SQL Server Management Studio

You will need SSMS or a similar query tool for this process.  Connect to the NAV database and get your query ready.  If you're looking for G/L Entry records, you'll want to get the last Entry No. value and setup your query accordingly.  The same thing goes for any other table you want to look at.  Get the last key values or the key values you expect and set your query up for that.

Notice the WITH (READUNCOMMITTED) - that's very important.  That allows you to read the data while it's part of an uncommitted transaction.

Step 3: Start your debugger and get the client ready to generate the error.

Now that you have a new service, start it up and connect to it.  Get the debugger going as well.  It needs to be running under the same debug service so if you are starting from the development environment, make sure you change your service instance under Database Information.

Important note: if you're working in a live environment, be aware that while this process is stopped in the debugger you have locks on any tables that you've written to.  If the error happened during posting (and they most often do), you will have the G/L Entry table locked until you continue under the debugger and finish the transaction.  That means you need to be quick or you need to work after hours.

When the consistency error comes up in the debugger, you aren't ready to query just yet.  You need to click "OK" before the data becomes available in SQL.

Once the debugger has the code opened (presumably on the commit causing the error), you can execute the second query in SQL.  Select the results in the result pane and use Ctrl+Shift+C to copy the data with headers and then paste into Excel.  Once the data is in Excel, press the continue button in the debugger to release any locks.

You can then review the data to determine what might be causing the problem.  In my experience, there are two main causes of inconsistency errors when posting in NAV: taxes and multi-currency.  By summing the amount column, you will most likely see that it doesn't balance although, in the case I was debugging for this article, it did.  It turns out that NAV hadn't inserted all of the entries when it did the final COMMIT.  There's a first time for everything.  I had to go back through with the debugger and pick out the last few entries.  Fortunately it was a small transaction and as usual, the problem ended up being taxes.

One final note:  nowadays with eventing, it may be just as easy to use a pre-commit event to dump records via XMLPort.  In this case, I was on a 2015 install so that wasn't an option.