Oracle to MS SQL Tutorial

This page is designed to set up connection to Oracle server. It is required to specify the following details here:

• Server - IP address or network name of Oracle server machine. If the database converter has been installed on Oracle server machine, you can specify "127.0.0.1" or "localhost" in this box.
• Database name - name of Oracle database. Enter the same title that you use with SQLPlus or any other standard Oracle applications.
• Port - port for connecting to Oracle. It is necessary to specify it only if it differs from default value 1521.
• Username - Oracle username for the database to which you are connecting
• Password - Oracle password for the database to which you are connecting

Sometime user enters all Oracle connection settings properly but still may get the following error: "Oracle data source does not exist. Check to verify correct name was given.". It means that the program failed to connect to the destination server using the selected method. To bypass this error you can try the following trick. Leave both "Server" and "Port" fields empty and enter in "Database" field the entire connection string as follows:

Of course, you need to replace "IP address of Oracle server" in the sample above with the actual value of IP address.

On this wizard screen you should enter the necessary settings to establish connection with Microsoft SQL Server. In "MS SQL Server" field you should specify the name of an instance of MS SQL Server to which you want to connect. If you leave it empty, the local server is assumed. Also, you can specify custom MS SQL port (other than 1433) in "MS SQL Server" field as follows:
 
Sql_server_name,1234
 
where 1234 is the custom port. Then you should specify the type of authentication to use when connecting to MS SQL Server:

• Windows authentication - SQL Server will validate the connection using the Windows NT user information. This option is only available when connecting to an instance of MS SQL Server on Windows NT. The client should be member of a Windows NT/2000 domain or workgroup. The user needs to be validated as a Windows NT user before access is granted.

• SQL Server authentication - SQL Server will use the standard security validation. To log in you should specify user name and password recognized on the target MS SQL Server.

In "SQL Server authentication" mode Oracle-to-MSSQL shows database tables owned by the specified user only. You can change it using "Connect as database owner" checkbox. It will force the program to display all tables from "dbo" tablespace, but only if the specified user has enough privileges to read them. Otherwise, the option will not take effect.

3. Select Databases

On this wizard screen you should specify Microsoft SQL database and basic conversion settings.

You can convert only table definitions of the original Oracle database. Select "Convert table definitions only" checkbox at the bottom of the wizard screen to use this feature. If you don't want to convert indexes select "Skip converting indexes" checkbox.

This wizard screen allows to manage Oracle tables to convert. In the left part of the window there is "Available tables" list box. It contains all tables of Oracle database except selected ones. On the right there is "Selected tables" list box.

To add new Oracle table for converting into Microsoft SQL format select it in "Available tables" list box and click "Add" button (or double-click the highlighted item). Use "Add all" button to select all Oracle tables for conversion.

To remove an item from "Selected tables" list select it and click "Remove" button. Use "Remove all" button to remove all tables from "Selected tables" list box.

Use "Add Query" button to convert results of custom SELECT-query. SELECT-queries can be used for filtering data, combining data from multiple tables, renaming or excluding certain columns, etc. See Using Queries article for the further information.