11 Replies Latest reply on Mar 10, 2008 3:10 PM by Dermot ONeill-Oracle

    SQL Server 200 to Oracle - problem loading objects

    user619067
      Here's what I've done so far:

      1. Download and install SQLDeveloper (C:\Oracle\sqldeveloper 1.2.1.32.13)

      2. Create omwbuser (w/ role=dba) in Oracle test database (hc02) (9.2.0.7.0).

      3. From Explorer, double-click sqldeveloper.exe

      4. File > New > Database connection (clicked Test button, but whatever it was that popped up, disappeared before I could read it.)

      5. Assuming it worked, click Connect button. Try a better test "select name from v$database;". Works.

      6. Migration > Repository Management > Create Repository ("Repository Built Successfully")

      7. Migration > SQL Server Offline Capture > Create Database Capture Scripts.
      Choose C:\Oracle\sqldeveloper\sqldeveloper\temp
      Generate for Windows Batch File
      Microsoft SQL Server (2000)

      8. Email scripts to Sql Server dba to execute. Get files back.

      9. Copy 16 SS2K_SYS<objectname>S.dat files to C:\Oracle\sqldeveloper\sqldeveloper\temp

      10. Migration > SQL Server Offline Capture > Load Database Capture Script Output
      Open C:\Oracle\sqldeveloper\sqldeveloper\temp\sqlserver2000.ocp

      Window pops up labelled "Capturing Database", but I don't see any entries under "Object Type" or "No of Objects Captured".
      Lower down in the pop up window it says "Capture Done".



      What am I missing?
        • 1. Re: SQL Server 200 to Oracle - problem loading objects
          jplogut-Oracle
          Please let me reproduce your steps and I'll get back to you.
          Regards,
          Jean-Patrick
          • 2. Re: SQL Server 200 to Oracle - problem loading objects
            jplogut-Oracle
            Hello,
            Could you please check if you have any error in the Migration.log (at the very bottom of the screen) ? Also, Could you please check if the table MD_TABLES (in the repository created in omwbuser) contains any lines ? Last please request the trace of the capture scripts when run...

            Thank you,
            Jean-Patrick
            • 3. Re: SQL Server 200 to Oracle - problem loading objects
              user619067
              "Could you please check if you have any error in the Migration.log (at the very bottom of the screen) ?"

              Thank you for the pointer, I didn't notice these before. I see 3 messages. Not sure what they mean though..

              Informational No valid catalogs found after skipping system and data dictionary catalogs
              Informational No User catalogs retrieved from database on current connection after filtering. System catalogs: master, msdb, tempdb and model have been skipped
              Warning Exception while Capturing :null


              "Could you please check if the table MD_TABLES (in the repository created in omwbuser) contains any lines ?"
              SQLWKS> select count(*) from omwbuser.md_tables;
              COUNT(*)
              ----------
              0
              1 row selected.


              "Last please request the trace of the capture scripts when run..."
              I will pass the request along to the SQL Server dba(s) and see what they can do.


              Thanks again for your help. I know I've got to be doing something wrong ... but darn if I can see it.
              • 4. Re: SQL Server 200 to Oracle - problem loading objects
                626987
                Hi,
                Iam a new user for this Oracle SQL Developer. when I capture the tables or any objects by either online capture or offline capture, the output message just says captured done in the a prompt window. The prompt window shows that it captured catalog 1 object and not any tables.

                The migration log says the following:
                * (Information Message ) No valid catalogs found after skipping system and data dictionary catalogs
                * (Information Message ) No User catalogs retrieved from database on current connection after filtering. System catalogs: master, msdb, tempdb and model have been skipped
                * (Warning Message ) Exception while Capturing :null

                The trace while doing the quick migration process is :

                WARNING: Unknown directive: SetSkipJ2SDKCheck
                Using oracle.home=D:\DATA\Agile\cct's\sky-cct\SQL Server to Oracle\Oracle SQL De
                veloper for Windows\sqldeveloper
                Using ide.user.dir=null
                Addin: Translator PlSql is trying to register a input type (.plsql) which confli
                cts with translator PlSql who already using this input type
                reloadBuffers(): 49 nodes checked in 4.32083E-4 second(s)
                reloadBuffers(): 49 nodes checked in 3.94121E-4 second(s)
                reloadBuffers(): 54 nodes checked in 0.001177051 second(s)
                java.sql.SQLException: a USE database statement is not allowed in a procedure or
                trigger.
                at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.j
                ava:364)
                at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2708)
                at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2150)
                at net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:687
                )
                at net.sourceforge.jtds.jdbc.TdsCore.submitSQL(TdsCore.java:849)
                at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1062)

                at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.
                java:520)
                at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPrep
                aredStatement.java:666)
                at oracle.dbtools.migration.workbench.plugin.SQLServer7AbstractCapturer.
                captureSchema(SQLServer7AbstractCapturer.java:547)
                at oracle.dbtools.migration.workbench.plugin.SQLServer7AbstractCapturer.
                captureObjects(SQLServer7AbstractCapturer.java:383)
                at oracle.dbtools.migration.workbench.plugin.SQLServer2000Capturer.captu
                reObjects(SQLServer2000Capturer.java:127)
                at oracle.dbtools.migration.capture.OnlineCaptureWorker.captureSchemaImp
                l(OnlineCaptureWorker.java:121)
                at oracle.dbtools.migration.capture.CaptureWorker.captureSchema(CaptureW
                orker.java:355)
                at oracle.dbtools.migration.capture.CaptureWorker.captureType(CaptureWor
                ker.java:276)
                at oracle.dbtools.migration.capture.CaptureWorker.runCapture(CaptureWork
                er.java:232)
                at oracle.dbtools.migration.workbench.core.ui.FullMigrateProgressRunnabl
                e.doCapture(FullMigrateProgressRunnable.java:411)
                at oracle.dbtools.migration.workbench.core.ui.FullMigrateProgressRunnabl
                e.doWork(FullMigrateProgressRunnable.java:212)
                at oracle.dbtools.migration.workbench.core.ui.AbstractMigrationProgressR
                unnable.run(AbstractMigrationProgressRunnable.java:159)
                at oracle.dbtools.migration.workbench.core.ui.MigrationProgressBar.run(M
                igrationProgressBar.java:565)
                at java.lang.Thread.run(Thread.java:595)
                reloadBuffers(): 54 nodes checked in 5.51087E-4 second(s)
                Oracle Jdbc tracing is not avaliable in a non-debug zip/jar file
                SELECT DISTINCT(REF_ID_FK), REF_TYPE FROM MD_ADDITIONAL_PROPERTIES WHERE PROP_KE
                Y IN (?) AND CONNECTION_ID_FK = ?
                reloadBuffers(): 54 nodes checked in 7.4104E-4 second(s)
                oracle.dbtools.sqlworksheet.sqlview.SqlEditorNode

                Iam not able to proceed further.
                Please pour in your reply as soon as possible.

                Thanks & Regards,
                Inigo Skylab
                • 5. Re: SQL Server 200 to Oracle - problem loading objects
                  564381
                  Hi Inigo,

                  Please check this post:
                  Re: Error when capturing from SqlServer 2005

                  I would also change the path to SQL Developer in order to avoid that single quote in the path.

                  Good luck,
                  Andrei
                  • 6. Re: SQL Server 200 to Oracle - problem loading objects
                    626987
                    Hi Andrei,
                    Thanks for the reply.
                    I have changed the path and now the path does not have any single quotes.
                    Now I have added jtds 1.2.jar file under third party JDBC Drivers and now Iam using the latest version of SQL Developer 1.5 EA2

                    When i try to capture the table from the source database, no table is getting caputed.

                    In the exception trace window, these are the the following traces

                    WARNING: Unknown directive: SetSkipJ2SDKCheck
                    DB Adapter warning: Replacing connection creator oracle.jdeveloper.db.adapter.My
                    SQLConnectionCreator for subtype MYSQL with oracle.dbtools.thirdparty.mysql.MySQ
                    LConnectionCreator
                    DB Adapter warning: Replacing connection creator oracle.jdeveloper.db.adapter.Or
                    acleConnectionCreator for subtype oraJDBC with oracle.dbtools.raptor.standalone.
                    connection.RaptorConnectionCreator

                    In the Migration Log, Iam getting the following message,

                    a USE database statement is not allowed in a procedure or trigger.


                    Thanks & Regards,
                    Inigo Skylab
                    • 7. Re: SQL Server 200 to Oracle - problem loading objects
                      Turloch O'Tierney-Oracle
                      Hi Inigo,
                      In the Migration Log, Iam getting the following message,
                      a USE database statement is not allowed in a procedure or trigger.
                      In 1.5EA2 there should be more exception details in the log (it needs to be clicked on).
                      I tried looking up the source of the error given above but the SQL did not seem to in a trigger or procedure. I am not sure if that is the current exception stack.

                      -Turloch
                      • 8. Re: SQL Server 200 to Oracle - problem loading objects
                        626987
                        Hi,
                        Even if i migrate single table.
                        The Migration log gives the same message as:
                        a USE database statement is not allowed in a procedure or trigger.

                        Kindly help me to proceed further.

                        Thanks & Regards,
                        Inigo Skylab
                        • 9. Re: SQL Server 200 to Oracle - problem loading objects
                          626987
                          Hi,
                          Here is the detailed explanation.

                          Iam using the latest version of SQL Developer 1.5 EA2 with the thrid party JDBC driver as jtds 1.2.jar file. I have created connections of SQL Server 2000 and oracle 9i.
                          I have created the repository and associated it. I can able to view the structure of SQL Server Objects like tables, views, procedures etc.,
                          All the pre-migration check got success.
                          When i try to do "Quick Migrate Microsoft SQL Server", Iam getting the following exception in the trace window..

                          WARNING: Unknown directive: SetSkipJ2SDKCheck
                          DB Adapter warning: Replacing connection creator oracle.jdeveloper.db.adapter.My
                          SQLConnectionCreator for subtype MYSQL with oracle.dbtools.thirdparty.mysql.MySQ
                          LConnectionCreator
                          DB Adapter warning: Replacing connection creator oracle.jdeveloper.db.adapter.Or
                          acleConnectionCreator for subtype oraJDBC with oracle.dbtools.raptor.standalone.
                          connection.RaptorConnectionCreator
                          Exception occurred during event dispatching:
                          java.lang.NullPointerException
                          at javax.swing.plaf.basic.BasicTreeUI.paint(BasicTreeUI.java:1215)
                          at javax.swing.plaf.ComponentUI.update(ComponentUI.java:142)
                          at javax.swing.JComponent.paintComponent(JComponent.java:742)
                          at javax.swing.JComponent.paint(JComponent.java:1005)
                          at oracle.ide.controls.JTreeTable$TreeTableCellRenderer.paint(JTreeTable
                          .java:309)
                          at javax.swing.CellRendererPane.paintComponent(CellRendererPane.java:134
                          )
                          at javax.swing.plaf.basic.BasicTableUI.paintCell(BasicTableUI.java:2071)

                          at javax.swing.plaf.basic.BasicTableUI.paintCells(BasicTableUI.java:1972
                          )
                          at javax.swing.plaf.basic.BasicTableUI.paint(BasicTableUI.java:1895)
                          at javax.swing.plaf.ComponentUI.update(ComponentUI.java:142)
                          at javax.swing.JComponent.paintComponent(JComponent.java:742)
                          at javax.swing.JComponent.paint(JComponent.java:1005)
                          at javax.swing.JComponent.paintWithOffscreenBuffer(JComponent.java:4963)

                          at javax.swing.JComponent.paintDoubleBuffered(JComponent.java:4916)
                          at javax.swing.JComponent._paintImmediately(JComponent.java:4859)
                          at javax.swing.JComponent.paintImmediately(JComponent.java:4666)
                          at javax.swing.RepaintManager.paintDirtyRegions(RepaintManager.java:451)

                          at javax.swing.SystemEventQueueUtilities$ComponentWorkRequest.run(System
                          EventQueueUtilities.java:114)
                          at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
                          at java.awt.EventQueue.dispatchEvent(EventQueue.java:461)
                          at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchTh
                          read.java:242)
                          at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre
                          ad.java:163)
                          at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre
                          ad.java:153)
                          at java.awt.Dialog$1.run(Dialog.java:515)
                          at java.awt.Dialog.show(Dialog.java:536)
                          at java.awt.Component.show(Component.java:1300)
                          at java.awt.Component.setVisible(Component.java:1253)
                          at oracle.bali.ewt.dialog.JEWTDialog.runDialog(Unknown Source)
                          at oracle.bali.ewt.dialog.JEWTDialog.runDialog(Unknown Source)
                          at oracle.dbtools.migration.workbench.core.ui.MigrationProgressBar.start
                          (MigrationProgressBar.java:338)
                          at oracle.dbtools.migration.workbench.core.ui.MigrationProgressBar.start
                          (MigrationProgressBar.java:233)
                          at oracle.dbtools.migration.workbench.core.ui.AbstractMigrationProgressR
                          unnable.start(AbstractMigrationProgressRunnable.java:127)
                          at oracle.dbtools.migration.workbench.core.FullMigrateInitiator.launch(F
                          ullMigrateInitiator.java:74)
                          at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.handl
                          eEvent(ObjectActionController.java:140)
                          at oracle.ide.controller.IdeAction.performAction(IdeAction.java:513)
                          at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:84
                          3)
                          at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:486)
                          at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:18
                          49)
                          at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.jav
                          a:2169)
                          at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel
                          .java:420)
                          at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258
                          )
                          at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
                          at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1
                          000)
                          at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMen
                          uItemUI.java:1041)
                          at java.awt.Component.processMouseEvent(Component.java:5488)
                          at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
                          at java.awt.Component.processEvent(Component.java:5253)
                          at java.awt.Container.processEvent(Container.java:1966)
                          at java.awt.Component.dispatchEventImpl(Component.java:3955)
                          at java.awt.Container.dispatchEventImpl(Container.java:2024)
                          at java.awt.Component.dispatchEvent(Component.java:3803)
                          at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212
                          )
                          at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)

                          at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
                          at java.awt.Container.dispatchEventImpl(Container.java:2010)
                          at java.awt.Window.dispatchEventImpl(Window.java:1774)
                          at java.awt.Component.dispatchEvent(Component.java:3803)
                          at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
                          at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchTh
                          read.java:242)
                          at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre
                          ad.java:163)
                          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)

                          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)

                          at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
                          SELECT DISTINCT(REF_ID_FK), REF_TYPE FROM MD_ADDITIONAL_PROPERTIES WHERE PROP_KE
                          Y IN (?) AND CONNECTION_ID_FK = ?

                          The Migration log message says like this :
                          a USE database statement is not allowed in a procedure or trigger.

                          Also, Iam not even able to migrate single table.
                          The Migration log gives the same message as:
                          a USE database statement is not allowed in a procedure or trigger.


                          Kindly let me know if am not clear in my explanation.
                          Kindly help me to proceed as soon as possible.

                          Thanks & Regards,
                          Inigo Skylab

                          Message was edited by:
                          Inigo Skylab
                          • 10. Re: SQL Server 200 to Oracle - problem loading objects
                            Turloch O'Tierney-Oracle
                            Hi Inigo,

                            Those stack traces do not appear related to your exception, can you get a stack trace from clicking on the log window? Right click view details.

                            Also:
                            -Note that we are using the latest jtds driver 1.2.2.
                            -What is the name of the database, does it have a ' for example?

                            -Turloch
                            • 11. Re: SQL Server 200 to Oracle - problem loading objects
                              Dermot ONeill-Oracle
                              Lets keep this discussion to 1 thread
                              Capture problem - can't proceed...
                              Regards,
                              Dermot.