Personalized Community is here!
Quickly customize your community to find the content you seek.
Check out the latest Business Central updates!Learn about the key capabilities and features of Dynamics 365 Business Central and experience some of the new features.
Overview | Guided Tour | Free Trial
2022 Release Wave 2Check out the latest updates and new features of Dynamics 365 released from October 2022 through March 2023
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
In an earlier blog post, we started to mention some tips and tricks to help you plan your upgrade to a newer version of Microsoft Dynamics NAV . In this post, we would like to share with you some of the known issues we stumbled over in the past couple of months or years, and what can you do in case you get into any of these known situations.
Today, it's mainly not about the solutions we give; sometimes it works at first try, sometimes you need to take another approach. We want to highlight how you should think about the different situations you get into, and what you should check for. So, based on our experience, we list here some of the main points that you need to understand carefully while facing some issues during your upgrade.
As you know, with Microsoft Dynamics NAV 2013 R2 , we introduced a number of changes to the product comapared to Dynamics NAV 2013 with the new support for multitenant deployments. You can read something about that here:
Based on this new architecture, compiling and synchronizing objects to SQL Server becomes more sophisticated than before. A well-known issue is that when you upgrade to Dynamics NAV 2013 R2 / NAV 2015 and open the Windows client for the first time, you would not be able to start the client and you would get the following error:‘Invalid Column name’ or ‘column xxx already exist’ or ‘Column xxx does not exist’.
To explain this in a better way, we published an explanation of the new design and how to work on it to avoid having this error:
Read that blog post before you start on step 11 in the upgrade from Dynamics NAV 2009 SP1/R2 to Dynamics NAV 2013 R2 , or step 7 in the upgrade from Dynamics NAV 2009 SP1/R2 to Dynamics NAV 2015. At that point, you are getting ready to import an .FOB file with all your objects. Based on the size of the database and the amount of customizations, you might want to export the objects to more to than one .FOB file so that you import the objects in a couple of separate rounds to simplify the first synchronization process. Here are the steps to follow:
Also do not forget to check if your remote query timeout (s) parameter in your SQL Server (In case it was changed before to any very low value).
If the .FOB file contains too many objects, you can create one .FOB file with table objects and another .FOB file with the rest of the objects. You would then import first the tables .FOB and synchronize the database schema, and then you import the second .FOB and sync. Alternatively, simply do not import all objects at once.
A message will show up, confirm by pressing Yes.
Import-Module "C:\Program Files\Microsoft Dynamics NAV\80\Service\NavAdminTool.ps1"
.. or run following command in PowerShell: get-help Sync-NAVTenant -online
In case the upgrade is to Dynamics NAV 2015, it is much easier, because it includes a status bar to show you the progress of the sync.
Get-NAVDataUpgrade InstanceName -ErrorOnly
Having all the above information in mind should make you fit in case of having sync. Issues while upgrading to avoid any unpleasant surprises.
Time estimated for the sync process, is always related to the amount of objects synchronized at once vs the amount of resources you do have on your SQL Server, note that you can be having lots of resources on the SQL Server machine, but you might have not configured it properly to get full use of these resources.
Before we start keep in mind that starting from Dynamics NAV 2013, the supported collations are only Windows collations, and this is for better support to the Unicode compatibility, so if you had a database on older version using SQL Collation, it will be changed automatically while upgrading. This can have an Impact on the Upgrade and can cause some known errors:
Another type of error, while trying to start step 2 to convert data is:
The following SQL error is not expected
The data type nvarchar cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Statement(s) could not be prepared.
This type of errors can occur if depends on whether you are using case sensitive or case insensitive collation, and if you have objects having different collations than other objects.
Usually it occurs in similar statements like the one below:
declare @p1 intset @p1=-1exec sp_prepexec @p1 output,N'@0 nvarchar(1),@1 nvarchar(1),@2 nvarchar(1),@3 nvarchar(1),@4 nvarchar(1),@5 nvarchar(1),@6 nvarchar(1),@7 nvarchar(1),@8 nvarchar(1),@9 nvarchar(30),@10 int',N'SELECT TOP 1 * FROM ( SELECT CAST (0 AS timestamp) AS [Timestamp], [Company Name], [ID] AS [Table No_], [Name] AS [Table Name], CAST (CASE WHEN [Row Count] > 2147483647 THEN -1 ELSE [Row Count] END AS int) AS [No_ of Records], CASE [Row Count] WHEN 0 THEN 0.0 ELSE [Size] * 1024.0 / [Row Count] END AS [Record Size], CAST (CASE WHEN [Size] > 2147483647 THEN -1 ELSE [Size] END AS int) AS [Size (KB)] FROM ( SELECT [Company Name], [ID], [Name], SUM(row_count) AS [Row Count], SUM(reserved_page_count) * 8 AS [Size] FROM ( SELECT C.[Name] AS [Company Name], O.[Object ID] AS [ID], O.[Name], C.[Name] + ''$'' + O.[Name] AS [Company Qualified Name] FROM [Sprügel].[dbo].[Object Metadata Snapshot] AS O CROSS JOIN [Sprügel].[dbo].[Company] AS C WHERE O.[Data Per Company] = 1 AND O.[Object Type] = 1 UNION SELECT '''' AS [Company Name], O.[Object ID] AS [ID], O.[Name], O.[Name] AS [Company Qualified Name] FROM [Sprügel].[dbo].[Object Metadata Snapshot] AS O WHERE O.[Data Per Company] = 0 AND O.[Object Type] = 1 ) AS [O] INNER LOOP JOIN [Sprügel].[sys].[dm_db_partition_stats] ON object_id = OBJECT_ID(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Company Qualified Name], @1, @0), @2, @0), @3, @0), @4, @0), @5, @0), @6, @0), @7, @0), @8, @0)) AND (index_id < 2 OR index_id = 255) GROUP BY [Company Name], [ID], [Name] ) AS [Core Query]) AS [Projected Query] WHERE ("Company Name"=@9 AND "Table No_"=@10) ORDER BY "Company Name" ASC,"Table No_" ASC',@0=N'_',@1=N'.',@2=N'"',@3=N'\',@4=N'/',@5=N'''',@6=N'%',@7=N']',@8=N'[',@9=N'',@10=0select @p1
This Union here is causing the issue based on the used collation because it is not matching what SQL server expects.
But you have to be careful while doing it manually.
It would be also good to understand what does the collation change exactly do, especially if you are changing the collation of a huge database:
ALTER TABLE "
(Loop cursor type of data upgrade), and after it finishes, the data would be returned, as you would be saved temporarily.
In huge databases, it is always recommended to do the change collation as a separate step in Dynamics NAV 2009 Classic Client before you even start the upgrade.
If you change the collation for a Dynamics NAV database using the NAV Development environment on versions 2013 R2 or later you might notice some subtle differences compared to earlier versions. See the following blog for more details: http://blogs.msdn.com/b/nav/archive/2014/12/04/how-to-change-the-collation-on-a-dynamics-nav-2013-r2-database.aspx
Data conversion is a very sophisticated process, and takes into consideration lots of are factors like the design of the tables and the relations of these tables so as the integrity of the indexes and the collation before working on converting the data itself.
If you have customizations that do not take care of all these dependencies you might get an issue while converting the data.
Let us look at an example:
Another example is a typically known error:
While upgrading and in some steps while compiling either system tables or customized tables, you can get an error such as this one:
However, sometimes you get another error, even though you have synchronized all objects :
As we mentioned before the upgrade procedure takes many factors into consideration. One of these factors is the indexes for the tables.
Message: the value of the SQL Server index cannot be null.
The performance of the upgrade might vary based on the size of the database and the size of the customizations, During the upgrade, there are 3 procedures that might be the bottleneck especially while upgrading huge databases:
Of course depending on the situation of the database, make sure before you start upgrading that you had maintenance plans running on the older database, and that all indexes and statistics are updated gradually so that the database does not have a general performance issue
In the oldest versions of Dynamics NAV, you could translate the columns in system tables to a language other than English. Starting with version 3.0, we adviced heavily against this, and versions later than Dynamics NAV 2013 R2 require that all columns in all system tables are in English. As a result, if you try to open a database with non-English system tables in Dynamics NAV 2013 R2 or Dynamics NAV 2015, you will see an error, saying that one or more columns do not exist.
if you are upgrading a database where the system tables are translated, do the following:
You can verify that you are running in the correct environment with English (US) as the base language by opening the ndo$dbproperty table in SQL Server Management Studio and verifying that, in the Identifiers column, the word Object is written exactly as that and not in any other way.
The example above is only one of the possibilities you might see in case if using other languages than English in naming system columns.
In more than one situation, you might encounter errors related to the naming conversion of your companies in Dynamics NAV.This is usually not an issue in Dynamics NAV 2015 or any older version; rather it is related to the upgrade routine itself.
If a company name includes a special character, or it starts with a number, you might get an error: ‘Incorrect Syntax near…’
We warmly recommend that you maintain a good naming convention, and try to avoid any special characters in company names.
Dynamics NAV 2015 introduces the concept of upgrade codeunits. We worked on the way schema synchronization and data upgrade works in Dynamics NAV 2015, because some of you experienced problems with the way database synchronization worked when we first launched Dynamics NAV 2013 R2.
Essentially, when you introduce changes to the database schema in Dynamics NAV 2015, Dynamics NAV will be always checking if these changes are destructive or not, and if there is a fear that these change might lead to some data being deleted (such as if you are dropping a table column so that the contents of that column will be deleted). If such as destructive change is detected, you will be prompted to handle the situation using upgrade codeunits.
This was explained on the community blogs more than once, but we suggest that you watch this video to understand what is really going on:
How Do I: Synchronize Database Schema Using Upgrade Codeunits in Microsoft Dynamics NAV 2015
On the other hand, if you think that these changes are not going to cause issues, you can take a backup of the database, and try to re-run the Sync-Nav Tenant command with the force Option manually again, but we rather advice you to be cautious when it comes to this option.
It is always better to run Sync-NAVTenant with check mode only to see what objects might be a source of destructive changes.
As mentioned before, we intended in this blog to summarize some of the tips and the tricks we learned while working on upgrade, some of these information might be basic for some partners, and some might be invaluable, at the end, I just wanted to share the information in a way that might make your life easier when you upgrade.
Microsoft Dynamics NAV Support EMEA
Business Applications communities