Upgrade Toolkit for Upgrading Data from Microsoft Dynamics NAV 2009 R2 and Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2013 R2


The components needed to upgrade data directly from Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2 are now available for download.

Download the upgrade toolkit from PartnerSource or from CustomerSource

In order to use the toolkit to upgrade data from Microsoft Dynamics 2009 R2 or Microsoft Dynamics NAV 2009 SP1, you will also need the Microsoft Dynamics NAV 2013 development environment and Microsoft Dynamics NAV 2013 R2.

At the Directions EMEA conference in Vienna and the Directions US conference in Nashville, we said that we have a team that is focusing on the Microsoft Dynamics NAV upgrade story. Going through the big transformation from the classic stack to the new product architecture in Microsoft Dynamics NAV 2009 was not easy and is challenging many customers and partners. So for our team, it is essential that the final outcome of our work is that each upgrade is easier, requires significantly less effort, and that it does not disrupt the daily business for our customers.

In Microsoft Dynamics NAV 2013 R2, we introduced support for converting a Microsoft Dynamics NAV 2013 database so that you can run an application that was created in Microsoft Dynamics NAV 2013 on the Microsoft Dynamics NAV 2013 R2 platform, also known as a technical upgrade.

Note: We strongly recommend that you upgrade the application objects as well so that your solution includes the important application fixes and new functionality that is introduced in Microsoft Dynamics NAV 2013 R2.

Secondly, you can fully automate the data upgrade process using the Windows PowerShell scripts that we included in the Microsoft Dynamics NAV 2013 R2 product media. Historically, this process has been known for its tediousness and high risk of human error when multiple operations had to be executed consecutively in all companies of the database that you were upgrading. The scripts automate this work so that you can test and execute your data upgrades more reliably.

Now with this delivery, we introduce an upgrade toolkit for upgrading the data from Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2. This significantly simplifies the upgrade process for those of you coming from Microsoft Dynamics NAV 2009 R2 – or Microsoft Dynamics NAV 2009 SP1.

Included in the new upgrade toolkit are all known data upgrade-related application hotfixes that we are aware of, and we also addressed several platform issues that affected the upgrade scenario.

Note: You must download the latest Microsoft Dynamics NAV platform hotfixes before you start using the upgrade toolkit. The required hotfixes for Microsoft Dynamics NAV 2013 are available in the latest hotfix rollup, which you can download from PartnerSource or CustomerSource. For Microsoft Dynamics NAV 2013 R2, you can download the required hotfixes from PartnerSource or CustomerSource.

When you download the new upgrade toolkit from the link above, you can use it to simply your upgrade process. Here are the main steps in upgrading from Microsoft Dynamics NAV 2009 R2 (or Microsoft Dynamics NAV 2009 SP1) by using the new upgrade toolkit.

Note

Before you start, make sure that you use the latest platform binaries for all versions of Microsoft Dynamics NAV in this process. For more information, see the following pages for the latest updates to the versions of Microsoft Dynamics NAV:

Microsoft Dynamics NAV 2009 R2: Overview of Released Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2.

Microsoft Dynamics NAV 2013: Released Cumulative Updates for Microsoft Dynamics NAV 2013.

Microsoft Dynamics NAV 2013 R2: Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2.

To upgrade data from a Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database

  1. In the Microsoft Dynamics NAV  2009 R2 or Microsoft Dynamics NAV  2009 SP1 development environment (Classic client):
  1. Make sure that all table objects have compiled successfully. During compilation, Microsoft Dynamics NAV generates or regenerates the BLOB content in the Object Metadata table that is used in the later steps.  In the Tools menu, choose Build Server Application Objects.
    Note: You must also do this if you upgrade from Microsoft Dynamics NAV 2013 to Microsoft Dynamics NAV 2013 R2 . All tables must be compiled Microsoft Dynamics in NAV 2013 before you start upgrading to Microsoft Dynamics NAV 2013 R2.
  2. Create a copy or a backup of your old Microsoft Dynamics NAV 2009 R2 database, and open it in the Microsoft Dynamics NAV 2009 R2 development environment.
  3. Add your partner license to the database.
    You can do this by selecting the Save License in Database field in the Alter Database window. If the field was not already selected, a dialog box opens so you can specify the location of your partner license.
    If the field was already selected, upload the partner license from the License Information window.
  4. Open the Object Designer, and then import Upgrade601701.1.fob. If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
  5. For each company in the database, open the company, and make the relevant changes to data. For more information, see Task 3: Data/Object Changes Prior to Step 1 in the MSDN Library.
  • Then, from the Object Designer, run form 104001, Upgrade – Old Version. Choose the Transfer Data button. Repeat this action for each company in the database. 
    Note
    We strongly recommend that you back up the database using SQL Server management tools after this step. 
  • When you have transferred all data for all companies, in the Upgrade – Old Version window, choose the Delete Objects button.
    This action deletes all objects in the database that are not tables, but also obsolete tables that belong to functionality that is not available in Microsoft Dynamics NAV 2013 R2.
  • Uninstall Microsoft Dynamics NAV 2009 R2, and then install Microsoft Dynamics NAV 2013.
    Note
    You do not have to install the full Microsoft Dynamics NAV 2013 product to perform the following database conversion. Instead, you can create a folder with the following files from the Microsoft Dynamics NAV 2013 product media:
    • Finsql.exe
    • Fin.stx
    • Fin.etx
    • CRONUS.FLF
    • Ndbcs.dll

    You can then use the Finsql.exe file to perform the conversion.  

  • Change the compatibility level of your database. For SQL Server 2008 and SQL Server 2008 R2, verify that the compatibility level of the database is set to 100. For SQL Server 2012, set the compatibility level to 110.
  • 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.

    Note
    We strongly recommend that you take a full SQL Server backup at this stage when the database conversion has completed.

  • Uninstall Microsoft Dynamics NAV 2013, and then install Microsoft Dynamics NAV 2013 R2.
  • In the Microsoft Dynamics NAV 2013 R2 development environment:
    1. Open the Microsoft Dynamics NAV 2013 database and agree to convert the database.
    2. Compile the system tables. You can find the tables by setting a filter for table ID 2000000004..2000000130.
      In the following step, you will be connecting a Microsoft Dynamics NAV Server instance to the database. 
  • Make sure that the service account that the Microsoft Dynamics NAV Server instance uses has the db_owner role for the database. 

    Note 
    If you are upgrading a large database, such as a database size of more than 20-25 GB, make sure that you increase the timeout value in the Microsoft Dynamics NAV Server configuration file. In the CustomSettings.config file, the value is defined in the SQL Command Timeout node. The default value is 10 minutes, which is sufficient in the normal day-to-day work. However, during an upgrade that can take several hours depending on the size of the tables, you must increase the timeout period.

  • Connect the Microsoft Dynamics NAV Server instance to the database, and then start the service instance.
  • In the Microsoft Dynamics NAV 2013 R2 development environment:
    1. On the Tools menu, open Options, and then, make sure that the Prevent data loss from table changes field is set to Yes.You must also make sure that the Server Name, Server Instance, and Server Port fields are filled in.
      This ensures that the Microsoft Dynamics NAV Server instance that is connected to the database will verify that no operation will cause loss of data in the development environment.
    2. Import all customized objects in .fob format into the upgraded database. If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
    3. Make sure all objects are compiled.

      Important
      It is very important that at least all table objects have successfully compiled before you proceed.

    4. Import Upgrade Step 2 objects from the Upgrade601701.2.fob file.If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
  • Perform the database schema synchronization by running the Sync-NAVTenant Windows PowerShell cmdlet in the Microsoft Dynamics NAV 2013 R2 Administration Shell (run as administrator).

    Note
    When you upgrade from one version to another, this typically involves numerous changes in the structure of the Microsoft Dynamics NAV tables (the object metadata). Database synchronization ensures that these changes in the metadata are applied to the corresponding tables in SQL Server. For example, it changes columns dimensions and data types, drops and create indices, drops and creates indexed views (V-SIFT), creates new tables, columns, and so on.

    When the number of changes is high, such as comparing Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2, this synchronization can become a lengthy and resource consuming task.

    Database synchronization is triggered upon any request to Microsoft Dynamics NAV Server, such as when you start a client or run a windows Powershell cmdlet. Microsoft Dynamics NAV Server checks if metadata has changed since the last synchronization by comparing the content of the Object Metadata and Object Metadata Snapshot system tables. If a change is found, the synchronization procedure is initiated.  

    When the synchronization process has started, it is essential that you wait for it to complete or rollback (in case of an error or time-out). If you’re running a client, do not confirm or click anything client side. If you’re running the Sync-NAVTenant cmdlet, wait for it to complete and return control to the Windows PowerShell command prompt.

    Do NOT stop the Microsoft Dynamics NAV Server service at this point since there are high chances that the database synchronization transaction is still running.

    1. Run Microsoft Dynamics NAV 2013 R2 Administration Shell as Administrator. This opens a PowerShell prompt where the Microsoft Dynamics NAV cmdlets are available.

    2. Run the synchronization command against your upgrade database as follows: 

      Sync-NAVTenant –ServerInstance <MyNAVServerInstance> 

    3. Wait until the cmdlet returns control to the Windows PowerShell window.

  • Open the Microsoft Dynamics NAV 2013 R2 Windows client to verify that you are connected to the Microsoft Dynamics NAV Server instance that is connected to the database being upgraded. 
    Next, you will run Upgrade Step 2 in each company in the upgraded database. You can get a list of all existing companies in the database by running the Get-NAVCompany cmdlet and passing as an argument the Microsoft Dynamics NAV Server instance which is connected to the upgraded database. 
    You will run objects directly from the development environment, so you must specify the company that the objects must run in in the Options window.
  • In the Microsoft Dynamics NAV 2013 R2 development environment:
    1. On the Tools menu, open Options, and then, in the Company field, specify the name of the first company.
    2. In the Object Designer, find page 104002 Upgrade – New Version, and then choose Run.
    3. In the Upgrade – New Version window, choose Test Database Connection to make sure that the C/AL code that is triggered by the actions on the page has access to the database.
      If your database is on a named SQL Server instance, you must specify the full name in the SQL Server Name field.  
    4. Choose Transfer Data.
      If the process is successful, and you don’t have to revisit the upgrade logs, you can clean the content of the Upgrade Time Log  table. 
    5. On the Navigate tab, choose Time Log, and then, in the Upgrade Time Log page, delete all records.
    6. Close the Upgrade – New Version window, and then close the Microsoft Dynamics NAV Windows client.
  • Repeat step 14 for each remaining company in the database.
  • When you have successfully transferred data in the last company, you must upgrade data that is common to all companies in the database, such as permissions, permission sets, web services, profiles and control add-ins.
  • Upgrade data common to all companies such as permissions, permission sets, web services, profiles and control add-ins.
    If the customer has changed the Read/Write/Modify/Delete/Execute settings for any of the standard permissions, or customized default permission sets in any way, you must merge these changes into the default permissions sets and permissions that are included in Microsoft Dynamics NAV 2013 R2. You can use XMLport 104001 Import/Export Roles and XMLport 104002 Import/Export Permissions to export the new default roles and permissions from the CRONUS International Ltd. demonstration database in Microsoft Dynamics NAV 2013 R2. You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client. For more information, see How to: Register a Windows Client Control Add-in. For example, the following client control add-ins are available from the Microsoft Dynamics NAV 2013 R2 product media:
    • Microsoft.Dynamics.Nav.Client.BusinessChart
    • Microsoft.Dynamics.Nav.Client.PageReady
    • Microsoft.Dynamics.Nav.Client.PingPong
    • Microsoft.Dynamics.Nav.Client.VideoPlayer
  • Delete the upgrade toolkit objects.
    1. In the Upgrade – New Version window, choose Mark/Delete Upgrade Toolkit.
      This deletes all upgrade toolkit objects, except tables
    2. In the Microsoft Dynamics NAV development environment, delete the upgrade tables by setting a field filter for objects where the Version List contains Upgrade Toolkit Table – marked for deletion.

    The database has now been through a data upgrade to Microsoft Dynamics NAV 2013 R2.

    Best regards,

    The Microsoft Dynamics NAV Service Experience team

    —–Blog post updated in May 2014—–

     

    Comments (53)

    1. Mikkel says:

      Hi guys, none of the links to partnersource seems to be working.

    2. Tom Wickstrom says:

      The link above for the 2013R2 hotfixes is not working. When you select it you receive the following.  The 2031 Hotfix link works fine. Thanks

      Our Apologies…

      This Page Could Not Be Found

      The page you are looking for has moved or no longer exists – or you might have entered an incorrect Web site address (URL). You might find what you are looking for in one of these areas:

      Learn more about Microsoft Dynamics Solutions including:

      Microsoft Dynamics CRM

      Microsoft Dynamics AX

      Microsoft Dynamics GP

      Microsoft Dynamics NAV

      Microsoft Dynamics SL

      For current Microsoft Business Solutions customers:

      CustomerSource

      For Microsoft Certified Business Solutions Partners:

      PartnerSource

    3. navblog says:

      Hello all,

      Sorry about the confusion! The links to PartnerSource for the hotfixes for Microsoft Dynamics NAV 2012 R2 are not yet active due to an internal delay. They will be available in a couple of days, we hope, and this will be announced on this blog as well.

      Meanwhile, the application hotfixes that you need are included in the upgrade toolkit.

      Best regards,

      The Microsoft Dynamics NAV Service Experience team

    4. dmitry_chadaev@hotmail.com says:

      Hi all,

      The links are now live!

      Best regards,

      The Microsoft Dynamics NAV Service Experience team

    5. tomcooper says:

      nice blog and information about Upgrade Toolkit for Upgrading Data from Microsoft Dynamics NAV 2009 R2 and Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2013 R2 and <a href="http://www.datasparc.com/download.html"&gt; soracle tools </a>.

    6. Mikkel says:

      Hi guys,

      Any ETA on the upgrade tool for W1 ?

    7. dmitry_chadaev@hotmail.com says:

      W1 and NO should be available latest 10/12/2013.

      Kind regards,

      Dmitry Chadayev & The Microsoft Dynamics NAV Service Experience team

    8. Uday Mer says:

      Not Working Properly for Indian Localization, After upgrading in NAV 2013 R2 it's giving error as below,

      The following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      Invalid column name 'Queue Folder UID'.

      Invalid column name 'Storage Folder Path'.

      Invalid column name 'Storage Folder UID'.

      Invalid column name 'Autodiscovery E-Mail Address'.

      Invalid column name 'Email Batch Size'.

      Statement(s) could not be prepared.

      And as I checked in both SQL, and development environment, the fields are there.

    9. Thomas says:

      Hi, I got the following Error executing the Page 104002 on Upgrade601701 with Build 7.1.35701.0, could you help me?

      Microsoft Dynamics NAV

      —————————

      Fehler beim Kompilieren von Assembly 'C:ProgramDataMicrosoftMicrosoft Dynamics NAV71ServerMicrosoftDynamicsNavServer$NAVISRV2013R2assemblyRecord104002_85.dll'. Dies kann auf Unterschiede zwischen Binärdateien in der Installation oder der Datenbank zurückzuführen sein. Stellen Sie sicher, dass alle Installationskomponenten konsistent und auf dem neuesten Stand sind. Fehlerdetails: 'c:ProgramDataMicrosoftMicrosoft Dynamics NAV71ServerMicrosoftDynamicsNavServer$NAVISRV2013R2sourceRecordRecord104002.cs(16,54) : error CS1729: 'Microsoft.Dynamics.Nav.Runtime.NavTextConstant' does not contain a constructor that takes 5 arguments

      —————————

      Regards,

      Thomas

    10. Sebastiaan Lubbers says:

      @Thomas: Did you restart the middle-tier?

    11. Thomas says:

      @Sebastiaan: Yes I did.

    12. Thomas says:

      Now it works, I have restarted the Service directly.

      If I restart (or stop and start) via Admintool it doesn't works.

      I think the Administration Tool must be closed while restarting the services, if the Tool isn't closed it didn't restart the whole process?!

    13. dmitry_chadaev@hotmail.com says:

      Hi Uday Mer,

      We've seen this issue before. It should not appear if you compile the system tables immediately after the second database conversion (as instructed in step 7b), before connecting the NST to the database. Once the system tables are compiled, you can connect the NAV Server to the database (or restart the server if it was already pointing to the database) and proceed.

      Hope this will help you.

      Kind regards,

      The Microsoft Dynamics NAV Service Experience Team

    14. Erik says:

      In step 9b I could not import the table objects because of this kind of errors:

      The operation could not complete because a record in the … table was locked by another user. Please retry the activity.

      With trial and error could import it if I compile 100 tables (doesn't matter if it gives error about not existing codeunit, forms, etc.) and then import 100 new table objects. And so on..

    15. Michael Kyhnel says:

      I get this error when i run the upgrade tool from 6 to 7.1 on some companies

      does anyone have any how to fix that ?

      Microsoft Dynamics NAV

      —————————

      A call to System.Data.SqlClient.SqlCommand.ExecuteNonQuery failed with this message: Column name 'DimVal1' does not exist in the target table or view.

      —————————

      OK

      —————————

    16. Theo Stock says:

      There are no links to the Upgrade Toolkits on the PartnerSource page – the Upgrade Tookit paragraph refers in the text to the downloads of the CU1 "above", which do not contain the Upgrade Toolkit (at least for W1).

      Were they removed again?

    17. Steven says:

      Hi guys, I get a permission error when I try to "Delete Objects", it says that I don't have permission to delete 7000000 codeunit using the upgrade toolkit. Do you know if that because of the type of licence?

      I can't also edit that codeunit from the object designer

    18. Mohammed Sami says:

      HI,

      Can any one help me to resolve the below errors :

      I have upgraded database Nav 2009 r2 to Nav 2013 r2  and after Converting database to Nav 2013 R2 i have imported Upgrade601701.IN.2 objects and while running RTC i have encountered below errors

      followed below link:

      blogs.msdn.com/…/upgrade-toolkit-for-upgrading-data-from-microsoft-dynamics-nav-2009-r2-and-microsoft-dynamics-nav-2009-sp1-to-microsoft-dynamics-nav-2013-r2.aspx

      —————————

      Microsoft Dynamics NAV

      —————————

      The following SQL error was unexpected.

      Cannot drop the index 'dbo.cronus$Prod_ Order Line.$13', because it does not exist or you do not have permission.

      The index '$10' is dependent on column 'Cost is Adjusted'.

      ALTER TABLE DROP COLUMN Cost is Adjusted failed because one or more objects access this column.

      —————————

      OK  

      ————————— —————————

      Microsoft Dynamics NAV

      —————————

      The following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      Invalid column name 'Queue Folder UID'.

      Invalid column name 'Storage Folder Path'.

      Invalid column name 'Storage Folder UID'.

      Invalid column name 'Autodiscovery E-Mail Address'.

      Invalid column name 'Email Batch Size'.

      Statement(s) could not be prepared.

      —————————

      OK  

      ————————- 

      Thanks !!!!

    19. Theo Stock says:

      Hi,

      While executing Step 9 d, I get a Table Lock error while trying to import the Upgrade Tool 2. it is "The Operation could not complete because a record was locked by another user. Please retry the activity." Investigating, it seems the NAV Service tier Service Account appears to be locking against the NAV Admin user account (performing the Upgrade), accessing the Object Meta Data Table.

      Has anybody had this problem, and how do I get past it? all previous imports of object had no problem..

      Thanks

      Theo

    20. Ben says:

      There's a bug with the upgrade toolkit in that it assumes the database to be upgraded is on the default SQL instance. Codeunit 104050 has the function GetServerName with this code:

       ActiveSession.SETRANGE("Session ID",SESSIONID);

       ActiveSession.FINDFIRST;

       ServerName := ActiveSession."Server Computer Name";

      The StatusLog table stores the Server Name and uses that as part of the connection string to open the database.

      The problem with this is that it doesn't identify whether the database is on a non-default SQL instance; you'll get connection errors when trying to transfer data or run Step 2. You can add a dirty workaround (add the SQL instance to the connection string directly, or add a 'SQL Instance' column to Status Log and update the functions accordingly), but thought I'd raise it here in case other people have the issue.

    21. Ben says:

      Never mind I'm an idiot – you just need to overwrite it on the upgrade page. Ignore me!

    22. Mohammed Sami says:

      Hi,

      I am getting a error while upgrading

      Microsoft Dynamics NAV

      —————————

      The operation could not complete because a record was locked by another user. Please retry the activity.

      —————————

      OK

      —————————

      —————————

      Microsoft Dynamics NAV

      —————————

      The operation could not complete because a record in the General Ledger Setup table was locked by another user. Please retry the activity.

      —————————

      OK  

      —————————

      Thanks !!!!!!!

    23. Kim says:

      So this seems NOT to be working anyway. I get similar errors as the others. Seems like 2013 R2 is NOT updating the objects correctly unless 2013 has been fully compiled with NAV server attached ??  Furthermore I can see that User table is not updated on SQL with the changes for R2 when following above procedure exactly. So I have to revert to first upgrading fully to 2013 and then afterwards to 2013R2

    24. VCM says:

      @Uday Mer

      Did you find a solution?

      I've got the same problem for Spanish Localitation:

      After upgrading in NAV 2013 R2 it's giving error as below,

      The following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      Invalid column name 'Queue Folder UID'.

      Invalid column name 'Storage Folder Path'.

      Invalid column name 'Storage Folder UID'.

      Invalid column name 'Autodiscovery E-Mail Address'.

      Invalid column name 'Email Batch Size'.

      Statement(s) could not be prepared.

    25. Ivan says:

      Can the power-shell cmdlets be used to automate the upgrade from NAV 2009 R2 ?

      As far I understand the database "App1" has to contain the application tables from the converted Microsoft Dynamics NAV 2013 database and the Upgrade Toolkit Step 1 objects.

      In that case the process cannot be automated, which means that this posting is a bit misleading.

      Thanks for your help.

    26. vremeni4 says:

      Hi,

      I am just in a process of upgrading a database from NAV 2009 R2 to NAV 2013 R2.

      Everything works well, until I get to the point where the NAV Server has to be started and connected to the upgraded database. (after customised objects are imported in NAV 2013 R2 database and compiled. But before Data is copied. )

      The NAV Server starts, connects to the database so everything is fine.

      Anyhow when RTC client tries to connect a time-out message appears.

      When I run SQL Profile NAV server executes constantly following SQL statements for all tables in the database :

      [code]

      IF (SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo."VIR_00_009$Sales Invoice Line"'), N'IsTable'))=1

      DROP TABLE dbo."VIR_00_009$Sales Invoice Line";

      CREATE TABLE dbo."VIR_00_009$Sales Invoice Line" ("timestamp" TIMESTAMP NOT NULL,"Document No_"

      ….. all fields here

      ,CONSTRAINT "VIR_00_009$Sales Invoice Line$0" PRIMARY KEY CLUSTERED ("Document No_","Line No_"));

      CREATE UNIQUE NONCLUSTERED INDEX "$1" ON dbo."VIR_00_009$Sales Invoice Line" (…);

      CREATE UNIQUE NONCLUSTERED INDEX "$2" ON dbo."VIR_00_009$Sales Invoice Line" (..);

      CREATE UNIQUE NONCLUSTERED INDEX "$4" ON dbo."VIR_00_009$Sales Invoice Line" (..);

      CREATE UNIQUE NONCLUSTERED INDEX "$5" ON dbo."VIR_00_009$Sales Invoice Line" (..);

      CREATE UNIQUE NONCLUSTERED INDEX "$6" ON dbo."VIR_00_009$Sales Invoice Line" (…)

      [/code]

      Even If I stop(kill) NAV Server this process continues.

      So I thought I will let it run until it finishes.

      The process finished at some point so I started the RTC Client and the whole process started again from the beginning. (WTF)

      Dropping the table, creating the table and all indexes.

      It looks like every time when RTC client connects, this process starts.

      I tried deleting all records from "Object Metadata" table and compiling all objects but no luck.

      I used Upgrade FOB from rollup 4 and also from original release, unfortunately the same behaviour.

      Is it possible to provide a hint what is wrong and what is going on.

      Thanks for your help.

    27. dmitry_chadaev@hotmail.com says:

      Hi vremeni4,

      It is important – are you also using the binaries from the Update Rollup 4?

      What you see there is the NAV Server synchronizing the changes introduced in C/SIDE (metadata) with the SQL database schema. When you import all new NAV objects in C/SIDE, the changes are not applied immediately to the corresponding SQL tables. It happens on a first request to the NAV server – e.g. when RTC connects to it (or when you run Sync-NAVTenant cmdlet from the Microsoft Dynamics NAV 2013 R2 Administration Shell).

      The timeout for this operation is regulated by the SQLCommandTimeout parameter in the CustomSettings.config file of the NAV server:

       <!–

         Timeout for Sql command

       –>

       <add key="SqlCommandTimeout" value="00:30:00" />

      If the process takes longer than 30 minutes (it can happen for large databases) you can increase the value.

      Kind regards,

      Dmitry

      Microsoft Dynamics NAV

    28. dmitry_chadaev@hotmail.com says:

      Hi Ivan,

      As you correctly noted NAV 2009 SP1 to NAV 2013 R2 upgrade is a manual process. The automation we released for NAV 2013 R2 can be used for upgrading the data from NAV 2013 to NAV 2013 R2, I apologize we did not make it clear in the intro of this post.

      The scripts we released for NAV 2013 R2 assume that App1 is an "old" application converted by the new C/SIDE. So in NAV 2013 case – the "old" application is NAV 2013 application running on NAV 2013 R2 executables. In NAV 2009 SP1 case – it would have to be NAV 2009 SP1 application converted by NAV 2013 and NAV 2013 R2 versions of the C/SIDE. This scenario is not officially supported by Microsoft, since we only support technical upgrade from a major release to its following minor releases, and also from minor to minor. In NAV 2009 case this is major to major upgrade – which requires full application upgrade.

      The cmdlets and some of the C/SIDE command line options used by the upgrade script are only available in NAV 2013 R2. Hence Step 1 and Step 2 must be run on the same binaries (NAV 2013 R2).

      Although we don't have any plans to introduce/support automatic data upgrade from NAV 2009, automation capabilities introduced in NAV 2013 R2 can certainly be leveraged to automate a part of the mentioned upgrade – e.g. Step 2 (invoking upgrade codeunits, importing setup data, initializing the companies etc. – all across multiple NAV companies, running in parallel). Please take a look at the scripts included on the NAV DVD for examples of how such actions could be scripted:

      WindowsPowerShellScriptsUpgradeCmdletsUpgradeToolkit

      WindowsPowerShellScriptsUpgradeCmdletsNAV

      Kind regards,

      Dmitry      

      Microsoft Dynamics NAV

    29. Juanan says:

      Hi, any solution?

      he following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      Invalid column name 'Queue Folder UID'.

      Invalid column name 'Storage Folder Path'.

      Invalid column name 'Storage Folder UID'.

      Invalid column name 'Autodiscovery E-Mail Address'.

      Invalid column name 'Email Batch Size'.

      Statement(s) could not be prepared.

      Thanks

    30. Vremeni4 says:

      Hi Dmitry,

      Thanks for your reply and it was very helpful.

      Everything works fine until the step where customised objects have to be imported; after the database conversion to NAV 2013 R2.

      When the import starts after a minute or two I get this error message :

      –snip

      This request operation sent to net.tcp://vmdev.globalmc.com:3945/2013r2_rollup4/ManagementService

      did not receive a reply within the configured timeout (00:01:00).  …..

      –snip

      I do understand the error message but I cannot find any config file where I can change the timeout.

      As RTC client connects for the first time to the database it starts SQL Schema synchronisation by running following code

      –snip

      IF (SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo."VIR_00_009$Sales Invoice Line"'), N'IsTable'))=1

      DROP TABLE dbo."VIR_00_009$Sales Invoice Line";

      CREATE TABLE dbo."VIR_00_009$Sales Invoice Line" ("timestamp" TIMESTAMP NOT NULL,"Document No_"

      ….. all fields here

      CREATE UNIQUE NONCLUSTERED INDEX "$1" ON dbo."VIR_00_009$Sales Invoice Line" (…);

      –snip

      If I stop NAV Server (and kill SQL Schema synchronisation processes on the SQL server) and try to import the FOB with custom objects again I get the same error message.

      So I thought I will leave the  SQL Schema synchronisation to complete.

      If I try that after 4 hours I get this error message in the Event Viewer

      –snip

      Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

      –snip

      I changed the <!–Timeout for Sql command–>

      <add key="SqlCommandTimeout" value="09:30:00" /> and the SQL Server was running happily all the time.

      If I try to connect with RTC client to the database I get a list of companies and after that this error message:

      –snip

      The following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      …..

      Statement(s) could not be prepared.

      –snip

      The only other way to force SQL Schema synchronisation to run is to use Powershell command Sync-NAVTenant

      After few hours I would get following error message :

      –snip

      Sync-NAVTenant : A connection to SQL server is no longer usable.

      This could be caused by one of the following reasons:

      * The server has been shut down manually or because of an error.

      * The SQL server connection settings are not correct.

      ……

      –snip

      I also tried compiling all objects, exporting and importing all objects, Build Server Application Objects.

      The same issue exist for NAV 2013 R2, NAV 2013 R2 Rollup3 and Rollup4.

      Interesting thing If I take the same database and I leave only 15 companies inside everything works fine.

      Any help is appreciated.

    31. MNC says:

      Please, any solution for this? ES Upgrade

      "

      he following SQL error was unexpected.

      Invalid column name 'Queue Folder Path'.

      Invalid column name 'Queue Folder UID'.

      Invalid column name 'Storage Folder Path'.

      Invalid column name 'Storage Folder UID'.

      Invalid column name 'Autodiscovery E-Mail Address'.

      Invalid column name 'Email Batch Size'.

      Statement(s) could not be prepared.

      Thanks"

    32. Dmitry Chadayev says:

      Hi everybody,

      Regarding "Invalid column name 'Queue Folder Path'" error, we already replied to it in the beginning of the thread:

      "We've seen this issue before. It should not appear if you compile the system tables immediately after the second database conversion (as instructed in step 7b), before connecting the NST to the database. Once the system tables are compiled, you can connect the NAV Server to the database (or restart the server if it was already pointing to the database) and proceed." Please let us know if you had compiled the system tables after conversion and restarted the server. It is essential that all steps in the flow are executed.

      Additionally – several issues related to data schema synchronization were fixed in the last few Update Rollups, so it is also important that you use the binaries of the latest available Update Rollup for NAV 2013 R2.

      Please note that this upgrade flow is the official upgrade flow, meaning that you can log support cases to Microsoft Support for the errors you discover during its execution. The errors logged to MS Support will be addressed more efficiently (and within our SLA) than if you post them in this blog. Therefore we would strongly recommend using this option. Please use this page to log a support request: mbs2.microsoft.com/…/newstart.aspx  

      Kind regards,

      The Microsoft Dynamics NAV Team

    33. MNC says:

      In my case, problem is solved by giving some more time to the sql server, after importing the customized objects, and before starting with step 2. SQL seems to need time to redesign the objects, or to "compile" all the metadata objects.

      best regards,

    34. Dmitry says:

      When do you plan to release upgrade toolkit for NAV 2013 R2 RU?

    35. VCM says:

      When do you plan to release "the upgrade toolkit"? (Without issues)

    36. Sathish says:

      Hi All,

      I currently try to upgrade a database from NAV 2009 R2 to NAV 2013. Now I'm on step 2. While upgrading I get the Error as below:

      "Dimension Value ID must have a value in Dimension Set Entry: Dimension Set ID=0, Dimension Code=PROJECT. It cannot be zero or empty."

      ](*,)

      Reply Pls, Im Struggling

    37. Mario says:

      Hi, is it possible to open the NAV 2009 R2 database directly with the NAV 2013 R2 (Build 36366) client to convert the database, or is the step over NAV 2013 necessary?

      Thanks and best regards

      Mario

    38. dmitry_chadaev@hotmail.com says:

      Hi everybody,

      2 updates:

      The upgrade toolkit for RU is now available for download (NAV 2013 R2 download page on the Partner Source).

      It is not possible to open NAV 2009 R2/SP1 database with NAV 2013 R2 development environment, it has to be opened with NAV 2013 C/SIDE first to perform Unicode conversion.

      Kind regards,

      Dmitry Chadayev

      Microsoft Dynamics NAV team.  

    39. Mario says:

      Hi,

      When I imported the .fob file (see step 9.2. in the list above) into the upgrade database I was not able to compile the table objects when I set the option "Prevent data loss from table changes" to "NO". Above it says that it should be set to "YES". When the option was set to "YES" for most table objects I got the error "Das Kommunikationsobjekt "System.ServiceModel.Channels.ServiceChannel" kann nicht zur Kommunikation verwendet werden, weil es sich im Faulted-Status befindet." The windows client was running. I am using the newest build 36366. Question: Is this a problem that I turned the option to "NO"? What could be the impact?

      Thanks for your answer.

      Best regards

      Mario

    40. Mario says:

      Hi,

      I made a mistake in the first sentence. Here is the correct version:

      When I imported the .fob file (see step 9.2. in the list above) into the upgrade database I was not able to compile the table objects UNLESS I set the option "Prevent data loss from table changes" to "NO".

      Best regards,

      Mario

    41. David Cox says:

      When converting the database 2009 R2 – 2012 – 2013 R2 the Dynamics NAV Database table "Tenant Property" does not get converted properly (latest build 6).

      In the Default Demo Database (7-1) the primary key is tenantversionno (int) and there is a field called license, in the converted database the key is tenantid (varchr) and the licence field does not exist, so when you restore and open on a different server you get a error on the license field.

      David

    42. Daniele Dettori says:

      Hi Mario,

         I had the same problem. Assuming that the "prevent data loss" option MUST be set to Yes when importing objects, I resolved with forcing the sync of the tenant (legacy environment)

      You have to open Dynamics NAV administration shell (with administrator permissions) and the string to run is :

      Sync-NAVTenant -ServerInstance [yourserverinstance]

      Hope this will be helpful

      Daniele

    43. gerdhuebner says:

      I get the same error as Sathish got, when trying to Transfer Data in Step 2 (11.2 above):

      "Dimension Value ID must have a value in Dimension Set Entry: Dimension Set ID=0, Dimension Code=KST. It cannot be zero or empty."

      It looks like a bug in the routine, which creates the new Dimension Set Entries…

    44. TheoS says:

      Following the above procedure for Upgrade of NAV 2009 SP1 database to NAV 2013 R2

      I am using NAV2013 Build 36605, NAV 2013 R2 Build 67221

      During Import of All Customised Objects in Step 11, Client Hangs indefenitly while apparently updating about the 25th Table (Not always the same table in the dialog).

      Looking at Task Manager No CPU activity on either Development Client or NAV Server Service.

      Looking at the SQL Server, NAV Server Process is Blocked by the NAV Development Client Process. None of the Processes on that Database is clocking up ay CPU or Physical I/O. Only the Wait Time clocks up to about 10 seconds then resets to 0 – perpetually.

      NAV Server SQLCommandTimeout is set to 23:30, and Database has been altered to not do Lock Timeout anymore – still the same result.

      Any Ideas from anybody?

      PS: I notice none of the Converted Table objects from NAV2013 are compiled any longer – is this normal?

      PPS: This is about my 8th Upgrade from NAV 2009 to NAV 2013 R2 (Using older version of the Platforms), and have not had this problem before..

      Thanks Theo

    45. TheoS says:

      PS to Previous posting:

      Here is what the NAV Server and Dev Clients are doing in SQL, where the NAV Service is Blocked by the NAV Client

      (NavServer): (@P1 int,@P2 int)SELECT [Metadata] FROM [MyDatabaseName].[dbo].[Object Metadata] WITH (UPDLOCK)  WHERE [Object Type]=@P1 AND [Object ID]=@P2

      (Development Client): (@lastKnownTimeStamp bigint)SELECT [Object Timestamp], [Object Type], [Object ID], [Change Type] FROM "MyDatabaseName".dbo."Object Tracking" WITH(TABLOCK) WHERE [Object Timestamp] > @lastKnownTimeStamp

      Looks like the Change listening in the Dev Client Might be Blocking the NAV server from performing anything?

    46. TheoS says:

      I am using NAV 2013 R2 Build 37221 (CU9).

      In Step 8 b. I found I had to Run the Sync-NAVTenant before compiling the system Tables, or get Error Code: 85132273 on some of the Tables.

    47. TheoS says:

      Relating to my Posting:

      "Here is what the NAV Server and Dev Clients are doing in SQL, where the NAV Service is Blocked by the NAV Client"

      It Turned out one of the Merged NAV 2013 R2 Tables had a Text that was shorter that one in the original NAV 2009 Custom object. Normally we would receive an error, but this time the NAV Development Environment just indefinitely

      Hope this helps

    48. Nathan Marshall says:

      Try upgrading some thing big and it doesn't work  

    49. Prem says:

      When I do this steps, I am having a Problem in Technical upgrade of the NAV2013 R2, The conversion from 2013RTM to 2013 R2 have an upgrade error saying Issues with reports… but actually it has only Tables… a bit confused.

      Cheer's

      Prem

    50. Michael says:

      Hi guys,

      could it be that there is a

      Sync-NAVTenant –ServerInstance <MyNAVServerInstance>

      missing between Step 11a and 11b? I get an error message when trying to import the customized objects, that the service is not reachable. Select “No” in the Developement Environment“Prevent data loss from table changes” would be an option, but not recommended?!?

      And one more thing – what is best practice for changing the collation during the upgrade from 2009sp1 to 2013r2? I have to change from SQL collation 1250 (Latin1, Eastern-Europe) to Windows collation German.

      Thanks in advance,

      Michael

    51. Raphi says:

      Hello

      I get several errors at number 18, where I have to delete the toolkit. I cannot delete the marked tables, because most of them are filled with entries (Code Field Information, Temp Payroll Setup, Temp Child etc.).

      Can I just empty these tables or did I something wrong?

      Best regards

    52. JaumeCC says:

      Hi !

      We have an error in a migration from NAV 2009 R2  (6.033413.0)  to  2013 R2

      The error is importing  Upgrade601701.ES.1.fob   file

      "There are errors in the text conversion because text no. 27-4000 does not exist in the .stx file. Internal error: 47-1"

      Change fin.stx and fin.etx for the contained in  ClassicENU or ClassicESP folders does not avoid the error.

      Any other people with that problem?

      Thanks in advance

      JaumeCC