NAV2013R2 table import not creating new fields in database

davmac1

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?

ara3n

Make sure the service tier is running. All database changes are done by the service tier.

davmac1

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.

davmac1

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.

thu

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

fufikk

Hi,

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?

Arndt_Loermann

Use the new Powershell Skript sync tenant to Transfer tablechanges from NAV to SQL in NAV 2013 R2

fufikk

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'.

AJust

hi,
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

ready...

and always after table changes start the wc...


Andreas

fufikk

That is a great explanation

Thanks & will try it out!

Djou2424

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.

davmac1

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.