locked
Error migrating Oracle table to SQL Server 2012 using SSMA RRS feed

  • Question

  • I tried to migrate an Oracle table to SQL Server 2012 using SQL Server Migration Assistant. I followed the instructions:

    In the Oracle Metadata Explorer, convert the Oracle Schema to Microsoft SQL database objects. In the SQL Server Metadata Explorer, synchronize the changes with SQL Server. In the Oracle Metadata Explorer, start to migrate the data. But I still got error: error occurred while inserting table information into data migration auxiliary table. reason: invalid object name 'mydatabase.o2ss.msssmadmtables'.

    Tuesday, March 24, 2020 3:37 PM

All replies

  • Hi ShenghuaNi

    Please refrence this to migrate Oracle table to SQL Server.
    And please refrence this case: error-migrating-data-error-occurred-while-inserting-table-information-into-data-migration-auxiliary


    Please make sure you haven't miss the following steps when you migrate the data:

    1. In the Oracle Metadata Explorer, convert the Oracle Schema to Microsoft SQL database objects.

    2. In the SQL Server Metadata Explorer, synchronize the changes with SQL Server.

    3. In the Oracle Metadata Explorer, start to migrate the data.

    Note:Do not manually create the database or tables in SQL Server. Don't forget to sync the changes to SQL Server before you start migrate data.


    Best Regards.
    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, March 25, 2020 7:58 AM
  • Hi, Yuxi,

    I had already done that. But still got the error.

    Best regards,

    Shenghua

    Wednesday, March 25, 2020 2:58 PM
  • Hi,

    SSMA creates the auxiliary table before and after the data migration, where it stores information about the indexes and constraints it disables, so that it can re-enable them back later. The error message suggest that SSMA could not access the table.

    Can you run SQL profiler against the database you are migrating and see which queries SSMA executes and why they fail? There are few possibilities that I can think of: (1) permissions (or rather lack of them); (2) case-sensitivity - maybe there is a bug in SSMA where it creates a table with one case, but subsequent query tries to access it with another? Is your SQL Server instance or database using CS collation?

    Thanks,
    Alex.

    Monday, March 30, 2020 10:30 PM
  • Hi ShenghuaNi,

    If the issue always exists, please don't hesitate to ask professional engineers for help, and they will deal with your problem separately and confidentially: 

    AssistedSupportProducts

    Best Regards.

    yuxi



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 31, 2020 1:15 AM