here's the complete list of what I did:
Environment: I'm running Windows 7 64-bit desktop, Oracle Linux 2.6 x86 64-bit, and Oracle 11g Enterprise Edition Release 126.96.36.199.0 64-bit. The source DB is MS SQL Server 2008.
1. Downloaded and installed JDK 1.7 on my desktop.
2. Downloaded SQL Developer 64-bit, installed in c:\app\sqldeveloper.
3. Connected to the target 11.2 database and create a schema ms_repo for migration, and linked it to migration
4. Downloaded 3d party jTDS JDBC driver jtds-1.2-dist.zip, copied jtds-1.2.jar to sqldeveloper\jlib directory. ( Also tried the jtds-1.2.5-dist.zip file).
5. Created connection to MS SQL Server named ms_agent2, and selected the database Agent.
6. Check for updates and installed updates for jtds-1.2.jar driver.
7. Checked the availability of MS SQL Server, was able to connect using "ms_agent2" connection, can see tables, data, list of procedures, and their bodies.
8. Started online migration process from Tools/Migration/Migrate. Also tried the right click on the "ms_agent2" entry in the list.
9. Within Migration Wizard, I selected:
- connection repository
- created a project and set the Output Directory
- choose Online method, and "ms_agent2" connection to MS SQL Server (On the left side this step is called "Source Database")
- clicked next, got an error posted in the original message. And the following screen does not show any entries in the "Available Databases" to select for migration.
The Oracle Tech Support was unable to figure it out what is wrong, and they are escalating this issue to the development team. But they were able to propose a workaround - an offline conversion, that really worked for me. I was able to export metadata of MS SQL server objects, including bodies of procedures, and data. And SQL Developer was able to migrate and create scripts for new objects in the target DB, including data. What surprised me, the tool even converted the T-SQL code to PL/SQL, and the output was successfully compiled in Oracle DB. Still stored code need some manual fixes, because exception handling and returning results to a caller are implemented in a different way in PL/SQL.
My overall impression of SQL Developer migration tool is positive
Just checked it using SQL dev 3.2 and 1.7 SDK (although only versions up to V1.6 are supported) - it works for me.
If you have some time, could you please get a Process Monitor trace file (www.sysinternals.com => process tools) from your SQLDeveloper session until the error happens and share it on a public server like Dropbox? Also could you upload to this location your JDBC driver (SQl Server)?
I could then compare both traces and also use exactly the same release like you...
I am facing the same issue/Errors. Here is my Environment.
1. Microsoft Windows 2012.
2. Microsoft SQL Server 2012.
1. Sun Solaris 11
2. Oracle Database 11gR2.
1. SQL Developer 3.2.2
2. JDK 1.6
During the Capture phase from SQL Developer i face the same errors that you face.I Understand that you used offline capture. However, in my case My DB is SQL Server 2012. There is no Drop Down option for SQL Sever 2012. Can you help me with the Steps of an Offline Capture, as its kind of confusing.
From the Support Matrix i understand that Neither SQL Server 2012 or Windows 2012 is supported/ compatible with SQL Developer 2012. Can you please Highlight your thought on this ?
I raised an SR with Oracle. They say this could be a potential Bug 17073424 Any thoughts or inputs ?
Guys, the mystery has been solved - it is the SQL Server 2012. I new it is not supported, and I ask developers about the version, and they assured me it's 2008.
Finally, I found the command to check the version while you're connected - "Select @@version", and I got:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervison)
But the good thing is that we have a workaround - I was able to complete offline migration.
Thank you all especially Klaus for your support.
the process is:
Prerequisites - you should have a MS SQL Server installed on your desktop/laptop, the offline process need the bcp.exe utility to run.
1. Install #d party JDBC driver for MS SQL Server (jtds-1.2 in our case), and register it in SQL Developer.
2. make sure your able to connect to SQL Server DB (you have IP address, DB name and credentials).
3. In SQL Developer 3.2.2, go to Tools/Migration, and select "Create Database Capture Scripts".
4. Select SQL Server 2008 from the drop-down list, and set the directory for scripts.
5. Run it, it will create two *.BAT files in selected directory.
5. Go to this directory, and run the OMWB_OFFLINE_CAPTURE.BAT script as :
---- To run this script, enter the following command at the prompt ----
---- OMWB_OFFLINE_CAPTURE login_id password database_name server_name
---- login_id is a login id which has been granted db_datareader
---- and view definition on database_name
---- password is the password for the login id
---- database_name is the name of the database you wish to capture
---- server_name is the name of the server on which the database resides
---- For example,
---- OMWB_OFFLINE_CAPTURE sa sapwd employeeDB DEPT1_SERVER
This script will use the bcp.exe utility to export metadata and data from the specified SQL SERVER DB to sqlserver2008.ocp file.
When it's done, you're ready for offline migration.
6. In SQL Developer, go to Tools/Migration/Migrate, follow the Wizard, select Offline Migration and use the generated sqlserver2008.ocp file.
There are sever settings that you can change to control options and the output of the process. The Migration process will generate one or many SQL files for Oracle.
7. Run generated scripts against your Oracle DB.
8. That's it.
The only concern I have is that if you have and advanced application using specific features of SQL Server 2012, then probably the Migration process will fail. And some manual fixes may be good in your app in Oracle DB after migration.
Hope it will help.