Troubleshooting MS SQL to Oracle converter

While working with MS SQL to Oracle conversion wizard you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error "There are orphaned records that prevent from creating foreign key..."
R: Foreign key cannot be created because there are one or more child records that do not have a matching record in the parent table. You have to remove these records in order to create the foreign key. The following query will determine if the entries in the child table have invalid foreign key references:

     select <column(s)> from <child_table>
     minus
     select <column(s)> from <parent_table>

where <child_table> - table with the foreign key, <parent_table> - table with primary key referenced by the foreign key, <column(s)> - one ore more columns included in foreign key.

P: Error "ORA-12500: the listener could not start a server process..."
R: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly. Verify that the ORACLE Server executable is present and has execute permissions enabled. Ensure that the ORACLE environment is specified correctly in LISTENER.ORA. If Oracle server is running under MS Windows NT/2000/XP, check that service 'OracleService<data source name>' (where <data source name> is an actual name of destination Oracle data source) is running on the database server machine.

P: Error "ORA-12505: the program could not resolve service name..."
R: The service name specified in the connection was not found in the listener's tables. Check to make sure that the service name is correct. The services that are currently registered with the listener can be obtained by typing: LSNRCTL SERVICES <listener-name>

P: Error "ORA-12705: Cannot access NLS data..."
R: The program failed to find specification of database language. You have to specify database language using "NLS_LANG" environmental variable. For example:

    set NLS_LANG=AMERICAN_AMERICA.UTF8

It the error persists, please read the following two articles giving detailed instructions how to diagnose and fix it:

http://www.dba-oracle.com/t_ora_12705_resolution.htm
http://www.dba-oracle.com/t_ora_12705_error.htm

P: Error "ORA-06413: Connection not open"
R: If you run MS SQL to Oracle converter on 64-bit Windows, the most probable reason of the error is that executable module mss2ora.exe has been placed into folder "...\Program Files (x86)\..." while Oracle does not allow parentheses in client application. Please copy all contents of product's installation folder into another location that does not contain parentheses in the path and run MSSQL-to-Oracle from there. If the error persists, please contact our support department at service@convert-in.com

P: I cannot see any tables after connecting to MS SQL database.
R: In "SQL Server authentication" mode MSSQL-to-Oracle shows tables owned by the specified user only. You can change it using "Connect as database owner" checkbox on "Connect to MS SQL Server" wizard page. 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.

P: I cannot uninstall SQL to Oracle converter.
R: If you need to remove MSSQL-to-Oracle product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel.

P: The application failed to start because OCI.dll was not found.
R: OCI.DLL is supplied by the Oracle Corporation and is a part of the Oracle Version 7.3.4 (or later) client software installation. Check that you have Oracle client and networking components installed on the machine where you run our software from. Also please make sure that the path to Oracle components folder is in $PATH system variable.

P: All table names are enclosed in quotes in the destination Oracle database.
R: By default MSSQL-to-Oracle converts table and column names in case sensitive fashion. Oracle requires you to enclose those names in quotes in SQL-queries. You can avoid it by selecting 'Make names uppercase' checkbox on 'Step 3 of 5' wizard page.

P: Error "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating MS SQL data into a new Oracle table, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending MS SQL data to an existing Oracle table, you should remove the conflicting unique index in the destination Oracle table.

P: Error "ORA-12541: no listener..."
R: The connection request could not be completed because the listener is not running on the database server. Start the listener on the destination machine using 'lsnrctl start' command.

P: Error "ORA-12545: connect failed because target host or object does not exist..."
R: Oracle suggest ensuring that the ADDRESS parameters (in the TNSNAMES.ORA file) have been entered correctly; the most likely incorrect parameter is the node name. Ensure that the executable for the server exists (perhaps "oracle" is missing.) If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again. It could be a network connectivity problem. Try pinging the host. This error has been observed on a laptop when the database was local, TCP/IP protocol used but the network card had been removed.

P: Some table names and/or field names in the resulting Oracle database are different from the original.
R: Oracle and MS SQL accept different sets of characters for table names and field names. When MSSQL-to-Oracle converts a name containing prohibited characters it replaces them with '_' in the destination database.

If you came across some other kind of problem while using MS SQL to Oracle converter, please contact our support department at service@convert-in.com