Common Issue in Upgrading NAV 2009 R2 to NAV 2013 R2

Hello,

On last month i am working on one domestic project Live Data Upgrade from NAV 2009 R2 to NAV 2013 R2.

I have face some issue in database upgrade process

So I want to share the issue and solution for that.

Issue 1 : When we convert the database in NAV 2013. System has shown following error

---------------------------

Microsoft Dynamics NAV Development Environment

---------------------------

The Permission table contains a Microsoft Dynamics NAV Development Environment field data type that is not compatible with the SQL Server data type:

Field: Security Filter

Type: TableFilter

SQL type: VARBINARY(252)

---------------------------

OK  

---------------------------

Solution:

After multiple tries of different workarounds I have found out when we restore the NAV 2009 R2 database by (*.bak) file than system has given that error in Database Conversion.

    

1) You need to take the full backup of NAV 2009 R2 Database in (*.fbk) file using developer environment 

2) Create new database in NAV 2009 R2 by using developer environment 

3) Restore the database by using (*.fbk) file and start the upgrade activity

Issue 2 : Error in converting database to NAV 2013 (second error)

---------------------------

Microsoft Dynamics NAV Development Environment

---------------------------

The following ODBC error occurred:

Error: [Microsoft][SQL Server Native Client 10.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

State ID: HY000

---------------------------

OK  

---------------------------

The ? collation is not supported by the Microsoft Dynamics NAV Development Environment.

Possible Reason behind  this error and solution:

1) In the Microsoft Dynamics NAV 2013 development environment, open the Microsoft Dynamics NAV 2009 R2 database and agree to convert the database. The database has now been technically upgraded to Microsoft Dynamics NAV 2013.

Note:

During this step, Microsoft Dynamics NAV converts all text and code fields to Unicode format by changing

their SQL Server data type. This conversion requires more disk space than usual, since both the database

and the log file will grow in size considerably. It can also be a lengthy process.

If your Microsoft Dynamics NAV 2009 R2 database is using SQL Server Collation, within the same step the

collation will be changed to a suitable Windows collation. This is because Microsoft Dynamics NAV 2013 and higher versions only support Windows Collation. If you converted your database using the RTM version of Microsoft Dynamics NAV 2013, you may be experiencing collation-related issues after this conversion. Therefore it is strongly recommended to use the latest available version of Microsoft Dynamics NAV 2013 development environment to perform this step.

Do not use the RTM Version of NAV 2013 (Base Release Version)

2) Check the windows collation of windows.

When you are upgrading any IN version database than it must be “English (India)”

See the following step to check the windows collation

Note: I have face this error when I was trying to convert the database in NAV 2013 at client side (client server). Because there is windows collation is “English (United States)”

3) 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:
It can happen that when the conversion starts conversion of object metadata the following error is received: The following ODBC error occurred: Error: [Microsoft][SQL Server Native Client 10.0] Unspecified error occurred on SQL Server. Connection may have been terminated by the server. State ID: HY000.

The main reason here might be some tables / any other objects in the database that do not compile because of some functions that are missing in the tables, or that do not maintain the design or the structure of other objects. For example when changes have been made to the tables directly in SQL.

Here again we mention that the database on the original version before the upgrade has to be checked to avoid these issues.


For a possible workaround here make a NAV backup of the NAV 2009 database and restore the backup into a new database and you will be able to convert the database.