SSMA V6.0 connection to Oracle 10g database fails

Question

I have an Oracle 10.2 database that I trying to move to SQL Server 2014 on a Widows Server 2012 R2 using SSMA 6 for Oracle but I am unable to connect to the Oracle database.  For the connection I have tried the Oracle Client Provider with TNSNAME mode, Standard mode and connection string mode.  The error returned is:

'Connection to Oracle failed. OCIEnvCreate failed with return code -1 but error message text was not available.'

The Oracle database is on a Windows 2003 Server. I am using the 32bit version of SSMA and the Oracle client installation is working fine, tested the connectivity to the Oracle server from the Windows Server using the system account with the following results:

TNSPing results OK

SQL Plus connects OK and I can select from the tables.

I created and tested a 32bit ODBC connection to the Oracle database which works fine.

I have previously connected and successfully migrated a similar database from the same server using SSMA 5.3 using a Windows 7 environment but this is no longer available to repeat.

I would be most grateful for any suggestions on how I can get this working.

Answer 1

Hi msdn_msg,

According to your description, your oracle is 32 bit, am I right? If that is the case, based on my search, you may get the error if you are using an unsupported version of the oracle client (older than 10.2) on Windows 2003.

To troubleshoot this issue, please check the version of oracle client that you have installed by typing ” tnsping localhost” command in Command Prompt, in this way, you can view that the oracle client is 32 bit or 64 bit.

Regards,
Ice fan

---

Ice Fan
TechNet Community Support

Answer 2

Thank you for your reply Ice Fan,

I am using the 32 bit Oracle client and the tnsping localhost command gave these results:

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-DEC-2015 10:41:32

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

As this was holding up progress I reverted back to a system with SSMA 5.3 using the same 32bit Oracle client and system configuration and this worked fine.  So I have stopped trying to use SSMA version 6 as I have successfully migrated the Oracle database to SQL Server using SSMA 5.3 which made the job very easy.  On the face of it this looks like a problem with version 6 of SSMA.

If you would like to try and identify the problem with version 6 I am happy to continue but my immediate need is now over.

Regards

Terry