Microsoft Dynamics NAV 2013 R2 Error - Unable to Verify Table Changes
What Every Developer Needs to Know
In NAV 2013 R2, Microsoft has introduced multi-tenancy. That means that a corporation that has many companies can split their database into many databases with one set of applications in an application database. The difference allows them a little more flexibility in backing up the databases and other maintenance procedures so that, for example, a world-wide corporation no longer has to take down a certain company during work hours to back up the database for the corporation as a whole. Additionally, completely unrelated companies can operate in a hosted environment with no customizations. But, this also brings changes in how we all (including single-tenant corporations) use NAV.
The greatest change is one that developers and NAV database administrators need to be aware of. When delivering a FOB (export of NAV application objects) that contains table changes to a database, we’re no longer updating NAV in one clean sweep. It’s no longer a matter of updating the objects, and SQL being updated as well. They are decoupled. I’m not a SQL expert so I’ll leave the explanation of what has changed under the covers to Microsoft. You can read about it here in the Microsoft Dynamics NAV blog. (I consider this a must read for NAV developers).
With these changes there has been lots of talk about pre-existing issues where there was a “possibility” of data loss. For that reason, all NAV2013R2 clients should be updated to the latest hotfix versions, including platform hotfix 36310 (per the above linked article).
But even on this version, there are some things you need to understand about delivery of table changes. And the easiest way to explain it is to show you the problem, and then the solution.
The Problem
Using a standard Cronus NAV2013R2 database with platform build 7.10.36310, I’m going to create a table change. I’ll add a new field, “Test Field” to our first table, “Payment Terms”.
Figure 1 – Table Change: Add field, Test Field, into the Payment Terms Table in Dynamics NAV…
I’ll exit, save changes and compile. There, field added, right? Well…let’s just see!
I then open SQL Server Management Studio, expand my database, find my table, open the COLUMNS section and…wait a minute! Where’s my field?
Figure 2 – Opening the same table in SQL Server Management Studio shows the new field is not there
In my disbelief, I recheck the Navision table layout and yes, my new “Test Field” is there! What kind of Hocus Pocus is this!
Let’s leave that example briefly and try another. (Yes, wash that off the brain, and let’s just move on.)
Now I try to import a set of FOB’s from my trusted ISV application. I open my brand spankin’ new database and import the FOB, only to receive this error:
Figure 3 – Importing a FOB into the database generates an error: “Unable to verify table changes.”
What kind of “verify table changes” are they trying to verify? Is something wrong with my ISV’s FOB? No, not at all!
Expecting that something is wrong with the FOB, I export out my Payment Terms table where I have added “Test Field”, and try to import it into my new database. Do I get the same error? No, not at all. The FOB loads completely. Yet, when I check the table in the new database – still no “Test Field”!
The Solution
After a few large doses of acetaminophen, a long conversation with Microsoft Support, and hours of reading blogs and forum posts, I can now tell you how to prevent this from happening. It’s really quite simple, and would have been ever so nice to have had in the Microsoft ‘What’s New’, ‘Release Notes’ or ‘Release Notes Follow Up’ documentation (Microsoft: This is my subtle hint and plea for future NAV version documentations.)
From your Development Client, open Tools – Options. You’ll see a few new settings we need to talk about. Namely those I’ve circled below.
Figure 5 – Selecting a Database Server Instance from the Database Information screen
Well, after you do this, and then (IMPORTANT PART) click OK on the selected instance that connects to your database, you’ll have populated the Tools – Options page fields related to the Server and Service.
(Note: If you open this page and it doesn’t show any instances, you need to start your instance and be sure your instance is running under the SQL “db_owner” user.)
Figure 7a and 7b – Difference in the table definitions as seen in NAV and SQL Server
“So! Now what?” you say. If I add the table in NAV, will it then remove it in SQL? Will my table metadata ever match my SQL database? Should I buy a plane ticket to a foreign country and change my name?
Say Hello to my little friend, PowerShell, and a cmdlet called “Sync-NAVTenant”. No, friends, tenant stuff isn’t just for multi-tenant. It’s for us single tenant folks too.
PowerShell comes as a separate application when you load NAV2013R2. You’ll find it in your Start Menu as “Microsoft Dynamics NAV 2013 R2 Administration Shell” (try saying that fast three times!). When you open the window it looks much like your normal command line window – except some commands are listed right about your entry point. The last one listed is our new friend “Sync-NAVTenant”.
What this little friend does is it makes your SQL database look like your NAV objects. How about that?
Just open PowerShell, type the command and enter.
The next prompt asks for the instance name, so we enter that. (We can find the instance name using the Tools Options page if we’ve updated it using Database Information).
Figure 8 – Use PowerShell to synchronize the SQL database and NAV objects
This runs for a while. How long depends on how many changes have to be made. My suggestion would be to run it EVERY time you import a FOB just to keep yourself in the habit. (Note: You have to be an administrator to run the command. A quick solution in Windows 7 is to right-click and choose “Run as Administrator” when opening PowerShell. If you run it without being an administrator, you’ll get a big blazing red screen of text that basically says you don’t have the permissions required.)
So…long story short (you needed to see the pain that you could inflict on yourself, right?), there are a few things a developer should do when importing a FOB to a NAV2013R2 database that has data.
- Set your instance using the Database Information assist edit button, and check your Tools – Options page to assure it has the correct settings (you can also just key these in, but for those of us who don’t want to trust our spelling and typing skills, assist edit is the way to go.)
- Run the PowerShell Sync-NAVTenant cmdlet – at least after any table changes but if you do it every time and it becomes a habit, that’s even better.
- Never NEVER nEVeR
never make table changes in the Production database. Oh my goodness, just don’t do it! Not only will your users that hit that first application that will sync the tables be ticked off at you (if they know why their job is running slowly, that is), but what if what if PowerShell runs long? What if you do cause data loss? How will you ever know what is lost and how to put it back? Let’s not fool around and test Fate. In the end, if we don’t have data integrity, we don’t have anything. - As the article I linked above suggests, make your table changes in small chunks. Now, I’m not sure what sizing requirements there are to “small chunk”, but it’s safe to say you don’t want to risk reaching your SQL timeout setting (also mentioned in the above article). (Did I mention that was a must read?J)
- Go ahead…check SQL. Open Management Studio and check the database table to see if your changes are there. It certainly doesn’t hurt anything!
If you enjoyed this blog, you might like to check out our collection of Development Blogs, as well as our “How To” blogs for practical advice on using Microsoft Dynamics NAV.