NAV2013R2 table import not creating new fields in database

davmac1davmac1 Member Posts: 1,283
edited 2014-04-07 in NAV Three Tier
I am doing an upgrade from NAV 2013 to 2013 R2 following the manual steps in the quick guide.
I added my field changes to a previously unmodified Cronus database (7.1), then exported all the objects.
Then I brought up my NAV 2013 database using NAV 2013R2 developer and accepted the database conversion. Then imported and ran the update 1 objects.
I imported my modified objects from the NAV 2013R2 (7.1) database and received an error on the update 2 about the new vendor ledger entry no. field missing.
I looked at the purch. inv. header table and determined that the new fields that had been added to the table and compiled in NAV do not exist in the SQL Server database table.
This problem only affects added fields. New tables are created as expected.

Any ideas/ fixes?


  • Options
    ara3nara3n Member Posts: 9,256
    Make sure the service tier is running. All database changes are done by the service tier.
    Ahmed Rashed Amini
    Independent Consultant/Developer

    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    davmac1davmac1 Member Posts: 1,283
    Thanks for the info Rashed.
    At the time of my initial object import the service tier was not running. I added it afterwards, but cannot get the changes to take, even if I disable the field, compile the table, and then enable the field and recompile.
    I will try redoing it with the service tier running first.
  • Options
    davmac1davmac1 Member Posts: 1,283
    I redid the conversion following the steps in the help server instead of the quick guide in MSDN.
    I found I had to set up the service tier after I had run the NAV 2013 to R2 using the developer and building server application objects (huge amount of errors).
    After I had the service tier successfully running (always refresh to make sure it has not stopped), I built the server application objects again - which then had no unexpected errors.
    The instructions on re-importing permissions do not work since it says not to delete super. I changed the xmlport to autoreplace and also to skip super.

    The automated upgrade will be more useful when there are a lot of companies - for one company, the manual way seems a lot quicker.

    I also set tools --> options --> Prevent data loss from table changes to No in the developer when I first brought it up.
  • Options
    thuthu Member Posts: 6
    Hi all,

    we also ran into the error of missing fields on the SQL side. The following steps seemed to help here:
    - export the table as .FOB
    - delete the table from the Object designer
    - delete the metadata of your table (found in table 2000000071)
    - re-import your .FOB file
    --> you're done!

    Regards, Thomas
  • Options
    fufikkfufikk Member Posts: 104

    I'm trying to add a new field to Company table (2000000006). In object designer table looks ok, in SQL it's missing the added fields. Any idea how to get around that?
  • Options
    Arndt_LoermannArndt_Loermann Member Posts: 15
    Use the new Powershell Skript sync tenant to Transfer tablechanges from NAV to SQL in NAV 2013 R2
    kind regards
    A. Loermann
  • Options
    fufikkfufikk Member Posts: 104
    I tried that, but no success:
    - I added a new "Test field" to Company table,
    - ran NAV-SyncTenant -ServerInstance XXX

    and got this as a result:
    Sync-NAVTenant : The following SQL error was unexpected.
    Invalid column name 'Test Field'.
  • Options
    AJustAJust Member Posts: 14
    this is a standard bug, fixed in Rollup 3

    The object metat data tables for the Nav-Client and the SQL-Server are in a different state!

    If you have a build before Rollup3 (7.10.36035) you must upgrade to that build no. or higher

    the windows client is responsible for the synchronization of these two tables. that means, you must start the wc after each changes on the tables (renamed table, rename fields, new fields and so on)

    Anyway.... your database is now demaged:
    We had the same problem here with multiple fields and tables and have developed with microsoft the following solution:

    Solution MS: go to the SQL-Server and create the field manualy :shock: :?

    Since we had the problem with multiple fields we have made the following:

    Try the following Workaround:

    create a new company in the database (there are all new fields updated)
    run a sql script that copys the data from the old company in the new one
    delete the old damaged company


    and always after table changes start the wc...

  • Options
    fufikkfufikk Member Posts: 104
    That is a great explanation :)

    Thanks & will try it out!
  • Options
    Djou2424Djou2424 Member Posts: 76
    I read the post and tried it for a 3rd time.
    I made sure the service tier was setup us and running properly
    But I'm still getting the same error.

    I was able to do it once, when I did a test with 2 companies only. The database I have to upgrade has 133 companies.
    Please help, I have no idea what to do next to be able to upgrade the database?

    The following SQL error was unexpected.
    Invalid column name 'Allow Blank Payment Info_'.
    Statement(s) could not be prepared.

    The following SQL error was unexpected.
    Invalid column name 'Payroll Trans_Import Format'.
    Statement(s) could not be prepared.
  • Options
    davmac1davmac1 Member Posts: 1,283
    This is one of those times when it is probably better to work with Microsoft support.

    I kind of doubt they tested this for 133 companies. You do realize that this has to update 133 tables each time you make a change.
    The change mechanism seems to be a lot less robust in 2013R2.
Sign In or Register to comment.