1 2 Previous Next 16 Replies Latest reply: Jun 28, 2011 11:46 PM by amin_adatia RSS

    SQL Server 2005 Migration Fails part of the way through

    amin_adatia
      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
          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
            and the source is => oracle.dbtools.db.DBUtil
            • 3. Re: SQL Server 2005 Migration Fails part of the way through
              Dermot Oneill-Oracle
              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
                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-Oracle
                  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
                    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
                      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-Oracle
                        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
                          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-Oracle
                            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
                              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-Oracle
                                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
                                  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-Oracle
                                    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