This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 28, 2011 9:46 PM by amin_adatia RSS

SQL Server 2005 Migration Fails part of the way through

amin_adatia Newbie
Currently Being Moderated
I am trying to capture an SQLServer 2005 database "schema definition". After the connection and loading into the Migration repository, the tool starts generating the scripts. It then fails at some point in the creating of the sequences script. How do I find out what the problem happens to be.
  • 1. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    The error I am getting is ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc
  • 2. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    and the source is => oracle.dbtools.db.DBUtil
  • 3. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    What version of SQL Developer are you using?
    What version of Oracle database have you installed the migration repository in?

    If you using SQL Developer 3.0 then the migration wizard can perform each step separately.
    It sounds like you have set the Preference
    Migration> Generation Options > File Creation Options to "A File per Object"

    If this is the case, can you attempt the generate the script with the option set as "One Single File".
    Set the preference first, then browse your Migration Projects navigator to the "Converted Database Objects" node.

    Choose Generate Target and in Step 7 of the migration wizard choose offline.

    Regards,
    Dermot
    SQL Developer Team.
  • 4. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    SQLDev v 3.0.04.34
    Database Oracle Express 10g
    generate the script option "One Single File".

    <<Migration Projects navigator to the "Converted Database Objects" node. >> I did not modify this so it would be whatever the default is set to. I only found the script for the sequences in the generated file although on the screen it shows doing a whole bunch of things beyond the sequences. In one of migration schema tables every object is shown as captured/converted status

    Another note : How do I get the SQLServer schema definition file so I can do this in offline mode?
  • 5. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    Just so I'm clear.
    If you right click on "Converted Database Objects" and choose "Generate Target".
    Then choose "offline" mode in step 7 of the migration wizard.

    1) The generation script pops up but it only has sequence definitions ?
    2) There are no CREATE USER or CREATE TABLE definitions in the same file?
    3) If you browse your "Converted Database Objects" can you see the expected tables / views/ .... ?
    Another note : How do I get the SQLServer schema definition file so I can do this in offline mode?
    This is called offline capture. First SQL Developer generates some scripts for you , which when run , will dump out the SQL Server metadata to dat files, which can be used to capture the database instead of directly through a connection.
    Tools> Migration > Create Database Capture Scripts


    Regards,
    Dermot
    SQL Developer Team.
  • 6. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    Hi!

    If you right click on "Converted Database Objects" and choose "Generate Target".
    Then choose "offline" mode in step 7 of the migration wizard. >>

    Yes

    1) The generation script pops up but it only has sequence definitions ? >> The generation script does not pop up. I just get a message that it failed and points to :null
    Unfortunately, I am not at the project office (Easter!) and will get back on Tuesday and so cannot post the exact message

    2) There are no CREATE USER or CREATE TABLE definitions in the same file? There are some procedures about data conversions for SQLServer and then the create sequence statements and just seems to have stopped writing to the file

    3) If you browse your "Converted Database Objects" can you see the expected tables / views/ .... ? There are 1440 tables and a whole bunch of other objects
    Another note : How do I get the SQLServer schema definition file so I can do this in offline mode?
    This is called offline capture. First SQL Developer generates some scripts for you , which when run , will dump out the SQL Server metadata to dat files, which can be used to capture the database instead of directly through a connection.
    Tools> Migration > Create Database Capture Scripts

    I could not figure out where or how to pass the values for the username, password, etc. These are all in commented (rem) out statements of the BAT files. Could not see any set statements if that is how the BAT files get their values or do I pass the values as part of calling the BAT file
  • 7. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    I ran the script "OMWB_OFFLINE_CAPTURE.BAT" passing the parameters for user,password, database and server
    The master and database directories get created
    I get a bunch of message lines with
    'bcp' is not recognized as an internal or external command, operable program or batch file.

    which seems to result from

    rem *** CALL THE BCP SCRIPT TO CREATE THE METADATA FILES

    call SS2K5_BCP_SCRIPT.BAT %1 %2 %3 %4 %OFFLINE_CAPTURE_COLUMN_DELIMITER% %OFFLINE_CAPTURE_ROW_DELIMITER%

    In the Table MD_REGISTRY I have
    PACKAGE     4
    SEQUENCE     1
    TABLE     41
    TYPE     10
    VIEW     24
  • 8. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    BCP is the SQL Server tool we use to dump out the metadata to dat files.
    To use the offline capture scripts you need to have a SQL Server client (which will include the BCP tool) installed on the same machine you are running the offline captured scripts from.
    If you have SQL Server or its client installed then the BCP tool will be available to you. Make sure its on the PATH first.

    Regards,
    Dermot.
    SQL Developer Team.
  • 9. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    I did the offline dump from a machine which had SQL Server Studio
    Got a bunch of .dat files and a bunch of zero record .err files in the master and <database> directories
    The previously generated .ocp file was not modified during this dump
    When I then do an offline migrate using the .ocp file I get a message
    Migration actions have failed. Check the migration reports for details : null

    and the directory specified for the output has no files.
  • 10. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    When you perform the offline capture, and you selected the ocp file.
    Are the master and <databasename> directories present with their dat files ?

    This directory structure is very specific, you cannot add/remove or move files to different directories.
    If you have ran the scripts and generated the dat files on one machine.
    Zip up the entire directory including the ocp file/ directories and dat files, persist the directory structure.
    Then you can move this zip file to where your SQL Developer install is , unzip it and proceed to capture.

    Also when performing the offline capture. Click "proceed to summary" so that you only perform the capture and none of the other steps.
    You can perform each step of the migration (capture/convert/generate/data move) separately and this helps identify where issues occur.

    Regards,
    Dermot
    SQL Developer Team.
  • 11. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    While waiting for the install of SQL Server Client software, I did and online capture and create to the Oracle Schema. I get things in the Captured and the Converted parts. Is there a way to read these "tables" and generate my own create table statements?
  • 12. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    You can create a script of all your converted model . If you run this script it will create objects in the specified (in the worksheet) Oracle target database.
    Have a look at this Sybase Oracle by Example. Sybase and SQL Server and migrated in the same fashion.
    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/SybaseMigration/SybaseMigration.htm#t3c

    You can see how to right click on your converted model and choose "Generate Target". Then choose the offline mode in the wizard.
    This will generates a script for you off all the ddl, but it does not run the script automatically for you, so you have a chance to review.

    Regards,
    Dermot.
    SQL Developer Team.
  • 13. Re: SQL Server 2005 Migration Fails part of the way through
    amin_adatia Newbie
    Currently Being Moderated
    No matter what I do, I end up with the error message "Migration actions have failed. Check migrations reports for deytails : null"

    I have no clue where to look for these migration reports!

    In Table MD_VIEWS I have a bunch of rows with
    Native_SQL = /* ddl not accessible */
    LANGUAGE = FailedSQL and MSTSQL

    Do I need to have Administrator rights???
  • 14. Re: SQL Server 2005 Migration Fails part of the way through
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Amin,

    All migration reports are visible when you click or double click on an item in the Migration Projects navigator.
    http://3.bp.blogspot.com/_H4TtZUn_Tz8/TVJ7LrdoLjI/AAAAAAAAGJw/WqPv50KyDVA/s1600/Status.png

    /* ddl not accessible*/ maybe the result of not having enough privileges to query the SQL Server metadata tables or the view text maybe encrypted on SQL Server.
    If you browse you SQL Server connection to the views. Can you click on a view and see its text?

    Regards,
    Dermot
    SQL Developer Team.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points