locked
Problems with migration from Oracle 10g database to SQL Server 2008 database RRS feed

  • Question

  • Dear friends,
    I am doing conversion from Oracle 10g to SQL Server 2008. SQL Server 2008 runs on computer with Windows 7. Firstly I have prepared schema of database with using of offer "Convert schema" in SQL Server Metadata Explorer.

    And then I started to migrate Data. But this problem appeared. SSMA wrote me, tables XY doesn't exist in target. But when I repeated to prepare its with using of Convert schema, SSMA wrote me - Tables XY already exist. But in SQL Server Management Studio it doesn't exist any new database schema. Could you help me, please? Anna


    Monday, February 4, 2013 2:17 PM

Answers

  • You can try to take a look here - http://msdn.microsoft.com/en-us/library/ms181091(v=sql.100).aspx

    If you don't know what Profiler is, you might not get that much out of it though but I'll suggest that you give it a try anyway.


    Steen Schlüter Persson (DK)

    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 10:01 AM
    Tuesday, February 5, 2013 9:09 AM
  • I have no idea - I never use any template...:-)

    When starting Profiler, you get a Trace Properties window and if you go to the Events Selection tab you can choose with Events and columns to show. If you don't have any other activity on the server you are using, you can just leave it all like it is and then start the trace. If there are other databases in use on the server, I'd suggest to click on the "Column Filter" button and set a filter on the databasename. Then you only see events for this specific database.

    Once you have started the trace, you can run your conversion tool until it fails. Then you go back to Profiler and check the events that has been captured. If you are lucky, you'll see some SQL statements that checks for the existence of the tables and/or some CREATE TABLE statements. This could give you a hint about what's going on when you run the tool.


    Steen Schlüter Persson (DK)

    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 10:01 AM
    Tuesday, February 5, 2013 10:16 AM

All replies

  • Have you tried to do a refresh of you tables in SSMS? If you right click on "Tables" under your servername in SSMS, it will refresh the view of tables and then your new tables might appear.


    Steen Schlüter Persson (DK)

    Monday, February 4, 2013 3:16 PM
  • Of course - Idid refresh many times, but no success. Could I send you screenshots of my problem?

    Anna

    Monday, February 4, 2013 4:19 PM
  • Hi Anna,

    You are welcome to post a screenshot in here, but I don't know if it's to much help. If you have refreshed and you still don't see the tables, then a screenshot might not make any big difference.

    Are you sure you are looking in the right database and/or in the right server/SQL instance when you look for the tables? If the I don't know the tools you are using for this, but I'd try to double-check what it actually do - i.e. which server does it connect to, which database and the name of the tables it creates.

    I'm quite certain that when it says the tables exists, then it's true but they might not exists where you expect the to be.

    If it still failes, then you can maybe elaborate a little more on the steps you are doing and maybe post a few screenshots from the process.


    Steen Schlüter Persson (DK)

    Monday, February 4, 2013 5:55 PM
  • Hi Steen,

    I am not total greenhorn in this area becouse I did the same type of migration with previous version of SSMA  in 2010  with success. There were converted cca 770 tables from our information system.

    But this situation is very strange.

    I am doing conversion from Oracle 10g to SQL Server 2008 (not R2) with using of SSMA v 5.2.1259.

    SQL Server 2008 runs on common computer with Windows 7. Oracle 10g runs on Server in our network. Firstly I have prepared schema of database with using of offer "Convert schema" in SQL Server Metadata Explorer.

    And then I started to migrate Data. But this problem appeared. SSMA wrote me, tables XY doesn't exist in target. But when I repeated to prepare its with using of Convert schema, SSMA wrote me - Tables XY already exist. But in SQL Server Management Studio it doesn't exist any new database schema. And final report: 0 tables were converted. Table XY doesn't exist in target. You must first convert the table ant then load it into the database.

    Could you send me your e-mail, I would like send you screenshots of this situation. My e-mail is situated in my profile.

    Thanks a lot,

    Anna

    Monday, February 4, 2013 10:18 PM
  • Hi Anna,

    I'll suggest that you post the screen shots in here so others can share their thoughts if needed.

    I don't know the SSMA tool, so I don't know in details what it does. You say you have prepared schema of database, but what does this means? Is that a script that creates the database and all the tables? It sound odd that the tool first says that the tables already exists and then afterwards that the table doesnt exists. Are you sure that you are using the same schema in SQL Server and not just a default schema that could be different for different users?

    Maybe you could also try to run a profiler while the tool is running. Then you might be able to see some of the SQL commands that are executed and then check what it is doing.


    Steen Schlüter Persson (DK)

    Tuesday, February 5, 2013 8:26 AM
  • Hi Steen,

    I dont know tool profiler. How can I run it?

    Anna

    Tuesday, February 5, 2013 8:49 AM
  • You can try to take a look here - http://msdn.microsoft.com/en-us/library/ms181091(v=sql.100).aspx

    If you don't know what Profiler is, you might not get that much out of it though but I'll suggest that you give it a try anyway.


    Steen Schlüter Persson (DK)

    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 10:01 AM
    Tuesday, February 5, 2013 9:09 AM
  • OK, I found it. Which type of template I can choose?

    Anna

    Tuesday, February 5, 2013 9:55 AM
  • I have no idea - I never use any template...:-)

    When starting Profiler, you get a Trace Properties window and if you go to the Events Selection tab you can choose with Events and columns to show. If you don't have any other activity on the server you are using, you can just leave it all like it is and then start the trace. If there are other databases in use on the server, I'd suggest to click on the "Column Filter" button and set a filter on the databasename. Then you only see events for this specific database.

    Once you have started the trace, you can run your conversion tool until it fails. Then you go back to Profiler and check the events that has been captured. If you are lucky, you'll see some SQL statements that checks for the existence of the tables and/or some CREATE TABLE statements. This could give you a hint about what's going on when you run the tool.


    Steen Schlüter Persson (DK)

    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 10:01 AM
    Tuesday, February 5, 2013 10:16 AM