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

VectorKVectorK Member Posts: 86
edited 2014-08-29 in NAV Three Tier
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.

Comments

  • VectorKVectorK Member Posts: 86
    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
  • postsauravpostsaurav Member Posts: 708
    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.

    Thanks & Regards,
    Saurav Dhyani

    Do you Know this About NAV?


    Connect - Twitter | Facebook | Google + | YouTube

    Follow - Blog | Facebook Page | Google + Page
  • VectorKVectorK Member Posts: 86
    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
  • postsauravpostsaurav Member Posts: 708
    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

    Thanks & Regards,
    Saurav Dhyani

    Do you Know this About NAV?


    Connect - Twitter | Facebook | Google + | YouTube

    Follow - Blog | Facebook Page | Google + Page
  • VectorKVectorK Member Posts: 86
    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
  • VectorKVectorK Member Posts: 86
    This is the last test results.

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

    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
  • VectorKVectorK Member Posts: 86
    And this is what we do and what we get. The same database and the same nav company.

    Imagen7_zpse38864a5.png

    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_HansenRob_Hansen Member Posts: 296
    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.
  • VectorKVectorK Member Posts: 86
    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
  • postsauravpostsaurav Member Posts: 708
    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

    Thanks & Regards,
    Saurav Dhyani

    Do you Know this About NAV?


    Connect - Twitter | Facebook | Google + | YouTube

    Follow - Blog | Facebook Page | Google + Page
  • VectorKVectorK Member Posts: 86
    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
  • atotovicatotovic Member Posts: 6
    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.
    Regards,
    Aleksandar

    http://totovic.com/
  • VectorKVectorK Member Posts: 86
    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!!
  • RCerratoRCerrato Member Posts: 8
    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.
  • VectorKVectorK Member Posts: 86
    So finally It works!!!!

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

    Thanks a lot to ALL!!!! =D>
  • RCerratoRCerrato Member Posts: 8
    You're welcome :whistle: \:D/
  • MaciMaci Member Posts: 65
    Ok, works, but... really? Is necesary to delete every time before exporting a company?
    :?
  • thegunzothegunzo Member Posts: 274
    :wink:
    ________________________________
    Gunnar Gestsson
    Microsoft Certified IT Professional
    Dynamics NAV MVP
    http://www.dynamics.is
    http://Objects4NAV.com
  • MaciMaci Member Posts: 65
    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
  • RCerratoRCerrato Member Posts: 8
    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 :)
  • jglathejglathe Member Posts: 639
    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
Sign In or Register to comment.