Skip to main content

NAV 2013 Upgrade Part IV - SQL Migration.


Hi all,

While Release of NAV 2013, we had some new things but some old things became obsolete and cannot be used with this version of Navision.

One of them that effect most of the customer is Support to NATIVE Database. So today we will discuss about Native to SQL Migration which is one of the steps during upgrade to NAV 2013.



Let's See why we cannot restore a backup of Native Database into SQL Server Database.

For Demo Purpose i have taken a demo database and intentionally created some issues which normally occurs during SQL Migration.

So i have NAV 2009 SP1 Native DB with Errors. I took NAV backup and trying to restore same on SQL Server NAV 2009 SP1 Database.

Here is the error message that i get -

The Cust. Ledger Entry table contains a field with a date value that cannot be used with SQL Server:
Record: Entry No.='1820'
Field: Posting Date='01-01-0101' (WHICH I CHANGED intentionally)



Reason -
SQL Date Starts From 01-01-1754.

*There are other errors too during restoring Native Backup to SQL.

Resolution-

We will follow the procedure given by Microsoft.

For this you will require Product DVD & Developer Licensce.

1) Navigate to Upgrade Toolkit ->SQL Migration in Product DVD.

2) you will find Migrate.fob.

3) Import Migrate.fob in Native Database(10 objects will be imported).

4) Run Codeunit 104010 Create Field Checking Code (This will create a text file in C Drive named as FIELDCHK.TXT.

5) If you are using windows 7 and the codeunit gives you an error message saying - "The operating system cannot access to the file C:\FIELDCHK.TXT .Verify that the file type and attributes are correct".

Then design the codeunit and onRun Trigger change the Path of the File Name to some other drive.

6) A message will popup which confirm that file is created.

7) Import the txt file into the database. (you require Developer Licensce to do that).

8) After importing compile Codeunit 104015 Field Check. After compilation Run Codeunit 104015 Field Check.

9) This process will take time as per the size of your database and no. of companies in your database.

10) The will will keep you updated about number of errors that have been found yet.


11) Once completed it will display a message with count of total number of errors.

12) Once clicked OK a form will get open which will contain the incorrect value and suggested values from tool.


13) You can change the New value as per discussion with client.

14) After doing all rectification just click ESC and the message will confirm that you want to update the new values in tables.

15) Delete all records from table 104010 Incorrect Data Value & 104011 Code Field Information FROM ALL COMPANIES.

16) If selected yes the new values will be applied.

17) After completion mark Codeunit 104015 and then filter based on version @*MIG* and mark all listed records.

18) Remove the filter and select all marked objects.

19) Delete all objects(Total object count should be 10).

20) Take Navision backup and restore on SQL Server Database.

Regards,
Saurav Dhyani
http://saurav-nav.blogspot.in/

Comments

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.