4 Replies Latest reply: Dec 22, 2009 7:28 PM by phelps RSS

    "Load database capture script output" failed when I migrate sql server 2005

    phelps
      I am migrating a Microsoft SQL Server 2005 database to oracle 10g. I encounter this problem when I run "Load Database Capture Script Output", It is extremely slow at this step, especially on loading constraints.I am using SQL Developer 2.1.0.63. The size of Microsfot SQL Server db is 6G.
        • 1. Re: "Load database capture script output" failed when I migrate sql server 2005
          Dermot Oneill-Oracle
          Hi Phelps,

          SQL Developer during the capture stage, only captures metadata , not the data within the tables.
          So its really the number of objects that your capturing that could possibly slow things down.
          A database with 100 tables, 100 procedures would be captured in about 2 minutes if everything is local.

          Is Oracle database where the migration repository is installed on the same machine as the SQL Developer instance your using ?
          Has the captured fishing yet? Is it hung ? or just capturing each each item very slowly.
          Are there any other processes on the machine SQL Developer is on or the machine your Oracle database repository is on that could slow them down?
          Did you attempt to capture more than 1 database offline ? Have you modified the directory structure or files in anyway?

          Regards,
          Dermot.
          SQL Developer Team.
          • 2. Re: "Load database capture script output" failed when I migrate sql server
            phelps
            Thanks for your reply

            1. Oracle 10g, SQL Server 2005 and SQL Developer are installed on same machine.

            *2. The caputing didn't finish, It failed while capturing a constraint, such as a SQL Server's Default Value CHECK.*

            3. I modified the file SS2K5_BCP_SCRIPT.BAT* by changing sys.DEFAULT_CONSTRAINTS TO sys.default_constraints on line "bcp "select definition,'%5',object_id,'%5', '%6' from %3.sys.default_constraints" queryout %3\SS2K5_DEFAULT_CONSTRAINTS.dat -c -e %3\SS2K5_DEFAULT_CONSTRAINTS.ERR -U%1 -P%2 -S%4
            ",
            If I didn't change it, It would raise a error on next step.

            4. I ran - OMWB_OFFLINE_CAPTURE sa "" dbname 127.0.0.1 - on command window.

            5. The database that I wanted to migrate has 1136 tables, 662 storage procedures and 638 constraints.

            All above is what I did.

            Thanks again.

            Phelps.

            Edited by: phelps on 2009-12-21 下午9:41
            • 3. Re: "Load database capture script output" failed when I migrate sql server
              Dermot Oneill-Oracle
              Hi Phelps,

              Thanks for this feedback.


              3. I modified the file SS2K5_BCP_SCRIPT.BAT by changing sys.DEFAULT_CONSTRAINTS TO sys.default_constraints
              This is interesting. I'm not sure why the script uses uppercase DEFAULT_CONSTRAINTS , but this hadnt caused a problem before. I checked our bugdb and nothing is logged.
              This would suggest that your SQL Server database is case sensitive. I am wondering if this could cause an issue. Maybe two tables named the same way but in different case.

              Your database is also quiet large. Sometimes weird things happen if you database tablespaces run out of space.
              Can you check the tablespaces used by the migration repository schema have enough space left.

              If you could try and perform the capture by running sql developer from the command line and see if anything appears in the command window or in SQL Developers log window.
              \sqldeveloper\sqldeveloper\bin

              Regards,
              Dermot
              SQL Developer Team.
              • 4. Re: "Load database capture script output" failed when I migrate sql server
                phelps
                Thanks for your reply

                1. I have migrated successfully by using jtds-1.2.4 JDBC 3.0, There are only 1 or 2 rows data failed in 8 tables.

                2. The disk space is enough.

                3. I didn't find any log files but datamodeler.log in \sqldeveloper\sqldeveloper\bin.

                4. I think the reason why it becomes slow is that sqldeveloper is checking constraints of SQL SERVER 2005 while it runs "Load database capture script output", But jtds doesn't.

                Thanks again.

                Phelps.