[Import-NAVData Nav 2013R2 Build 36897] Database Schema Prob

VectorK

Hi all,

We need to import a company via the new page: Import Data (9900)

Even the source and the target dabases, both have the same objects, and we always receive the following message error:
"Cannot import the data because the database schema in the database is different from the schema for the data that you want to import".

I've tried the same process with a Chronus Company and it works!!! ](*,)

Could you help, please?

Thank you for your help in advance.

VectorK

It Happens the same I use the cmdlet:

Import-NAVData -ServerInstance myinstance -CompanyName "0 - Company" -FileName c:\myfile.navdata

the system returns:

Import-NAVData : Cannot import the data because the database schema in the database is different from the schema for the data that you want to import.
At line:1 char:1
+ Import-NAVData -ServerInstance myinstance -CompanyName "0 - Company" -FileNa ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (0:Int32) [Import-NAVData], FaultException`1
+ FullyQualifiedErrorId : MicrosoftDynamicsNavServer$myinstance,Microsoft.Dynamics.Nav.Management.Cmdlets.ImportNavData

postsaurav

hey VectorK,

There is some changes in the database schema where you are trying to import.

Examples -
a.) A new table have been created in target?
b.) A existing table have been modified (Like Field Deletion/ Addition).

1. Create a Backup of your database (Old Database).
2. Restore the backup with a new name(New Database).
3. Take Backup from Old Database (Single Company with NOT - Data Conman to all companies & NOT - Application Objects).
4. Restore the backup in New Database.

Let me know the issues that you face.

VectorK

postsaurav wrote:

There is some changes in the database schema where you are trying to import.

Examples -
a.) A new table have been created in target?
b.) A existing table have been modified (Like Field Deletion/ Addition).

As I wrote at the beginning, There are no changes between database.
Even I've export&Import all the objects in order to be completly sure.

postsaurav wrote:

1. Create a Backup of your database (Old Database).
2. Restore the backup with a new name(New Database).

These two steps above, you mean via SQL?

Another test I've done: Export-NAVdata and immediatly after try with Import-NAVData, and I still get the same error
(I mean, in the same database)

Thank you very much

postsaurav

Hey VectorK,

Can you tell me which of the below options you are selecting while Exporting / Importing the backup .

1. All Companies
2. Global Data
3. Application Data
4. Application

Because i have tested the same and its working fine.

Below are the links where i posted the steps but in my case it worked fine...

http://saurav-nav.blogspot.in/2014/06/nav-2013-r2-how-to-backup-selected_1347.html

http://saurav-nav.blogspot.in/2014/06/nav-2013-r2-how-to-restore-selected_21.html

VectorK

Hi Saurav,

The selection option to export was selecting just one company NOT Global Data, NOT Aplication Data, NOT Aplication

And when importing the command looked like this:

Import-NAVData -ServerInstance myinstance -CompanyName "0 - Company" -FileName c:\myfile.navdata

Have you tried with a customized company/database?

Another clue: The Database was reciently migrated from 2009R2 (Maybe It helps)

Thank you so much again

VectorK

This is the last test results.

Exporting and Importing data from the same nav company & database.


My Customer is getting nervous about it, because nobody can't understand that something that seems so easy, is so difficult today in Nav.

Please, help [-o<

Thanks in advance

VectorK

And this is what we do and what we get. The same database and the same nav company.



So, we need to know if there is another way to export and import a nav company, just as we used to do in older versions

Thank you

Rob_Hansen

Assuming you're on cumulative update 9, you need to submit this issue to Microsoft and let us know the outcome. If you get the error when exporting and immediately trying to import back into the same database, there's clearly an issue with the function that MS needs to look into.

VectorK

rdhansen wrote:

Assuming you're on cumulative update 9,

Yes, I upgraded the solution, thinking that the "bug" could be "debugged" in the new release, but unfortunatly, it doesn't.

rdhansen wrote:

you need to submit this issue to Microsoft and let us know the outcome.

I'd do it but I'm not a Partner. I hope that some Partner will do it for me (if they read the thread)

rdhansen wrote:

If you get the error when exporting and immediately trying to import back into the same database, there's clearly an issue with the function that MS needs to look into.

Agree =D>

Thank you very much

postsaurav

hey Vector,

Microsoft have come up with a video tutorial for same. Please have a look in the below link -

http://msdn.microsoft.com/en-us/dynamics/nav/dn792157.aspx

VectorK

Thank you very much Saurav.

I've seen the video. There's nothing new on it that helps me.

Last weekend I've tried with all the ways, and even all the posiblities to export and import nav data. Via nav client, via powershell, via NST, via Database, and so on...
Always I've got the same result: Schema error

This functionality seems only work with Cronus Companies. (Microsoft, please test your product with REAL companies, and remember that your customers are not your testers).

You'll always get the error schema when exporting and immediately trying to import back into the same database (or a new one with the same objects)
There's clearly an issue with the function that MS should look into.

Thanks again Saurav

atotovic

Hi VectorK,
Are you checked collation (must be the same) and maybe you created some table directly through SQL?
But, since you wrote you made upgrade for earlier NAV version, I think that you can find resolving of your problem here: http://www.dynamics.is/?p=1908
Gunnar already wrote about this problem and how you can resolve it. You will find here all details.

VectorK

Hi Atotovic,

No tables were created directly through SQL and the collation are exactly the same in both databases.

Let me try the Gunnar's solutions and I'll tell you the results as soon as possible.

Lot of thanks!!

RCerrato

Hi Vector, hi all,

I was facing your same problem and finally got it working thanks to this link: http://www.dynamics.is/?p=1908

I tried to export / import without any changes in the same database but "THE SCHEMA IS DIFFERENT".

It seems to be an issue with the data stored on Object Metadata Snapshot for Virtual Tables. So that mainly you will need to delete these records BEFORE exporting the data file.

I copy paste from the sql command to run in the SOURCE DB before EXPORTING data file:

delete from [SOURCE DATABASE TO EXPORT].[dbo].[Object Metadata Snapshot]
where [Object ID] in
( 2000000007, 2000000009, 2000000020, 2000000022,
2000000026, 2000000028, 2000000029, 2000000038,
2000000039, 2000000040, 2000000041, 2000000043,
2000000044, 2000000045, 2000000048, 2000000049,
2000000055, 2000000058, 2000000063, 2000000101,
2000000102, 2000000103)

I hope it helps you all.

VectorK

So finally It works!!!!

Just deleting that "rubish" the "bug" was "debugged"

Thanks a lot to ALL!!!! =D>

RCerrato

You're welcome :whistle: \:D/

Maci

Ok, works, but... really? Is necesary to delete every time before exporting a company?
:?

thegunzo

Maci

More problems:

I need to do that in a real environment. I have 3 source databases, and I want to export one company from every one. And restore the 3 companies in a unique destination database.

I execute the SQL script in the 3 source DB, before make the companies backup.

In the destination, I can restore 2 of the backups, but I get the same "database schema error" in the third.

Any idea? Is the script complete?

Thanks in advance

RCerrato

Hi Maci,

I guess this time you really have a DB Schema issue. Make sure you have the same table objects in all of them. A quick tip I could give you is export all the hashes from the sources and double check in an Excel Document that all the hashes match.

--Script to get the hashes
select
db1.[Object ID] "DB1 Table ID",
db1.Name "DB1 Table Name",
db1.Hash "DB1 Metadata Hash"
from [Destination Database].[dbo].[Object Metadata Snapshot] db1
--Script to get the hashes

Then after you export the hashes from all the sources and the destination you can just use excel to double if you are missing any object to recompile / import or whatever the case is.

Let me know how it goes

jglathe

Hi RCerrato,

I know you only want to be helpful. @MSFT: Really, WTF? What kind of brain-dead design failure is this? The restore only works when the database schema matches? And when, in the real world, is this the case? Only, and I mean ONLY, when you do a backup and try a restore immediately afterwards, and nobody has changed a single table in the database inbetween. That's not how reality is, to be frank. And I - for the hell of it - cannot understand how this can be made a fscking requirement for a restore of DATA. We don't do this for fun, we have to WORK with it. The real-world use cases should be known to you. But apparently, you don't know them. It was not our highly-repeated wish that you remove the .fbk backup/restore feature, it was your idea (for whatever reason). After the negative feedback, you took 9 MONTHS to come back with a non-working solution. What's wrong with you? Time to get a clue on what you really want.

@all: Sorry for the rant. The bullshit is getting to me.

with best regards

Jens