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

    Question

  • Hi, All:

    I am using the tool SQL Server Migration Assistant for Oracle to move an existing Oracle onto sql server. However, I can't connect to a source Oracle database from this tool, even though I can use PLSQL developer to do Oracle connection. I have googled Internet and follow suggestions to make security permission changes on Oracle client home page. Unfortunately, it is failed with the following error:

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

    Please help me use SQL server instead of Oracle.

    Oracle client home is: C:\app\oracle\product\11.2.0\client_1

    Regards,

    Monday, September 23, 2013 8:56 PM

Answers

  • Hi, David:

    I finally make the progress that I find the problem and solve it. Thanks a lot for your effort!

    Tuesday, September 24, 2013 2:42 PM
  • Hi,

    I’m glad to hear that you found a good idea to resolve your question.If you find out the root cause finally and share your solutions & experience here, it will be very beneficial for other community members who have similar questions.

    Thanks

    Candy Zhou

    Wednesday, September 25, 2013 9:44 AM

All replies

  • SSMA installs both a 64bit and a 32bit version, and there are two shortcuts in the start menu (or start screen).  If you have the 32bit Oracle drivers, you need to launch the 32bit SSMS.  If you have the 64bit drivers you need to load the 64bit SSMA.  The oracle driver bin folder (containing oci.dll) also needs to be in the path.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, September 23, 2013 9:10 PM
  • Hi, 

    Thanks a lot for quick reply!

    I am using win2k3 server on a virtual machine. The tool is SSMA for Oracle 5.2.exe. It is 32bit ssma. I can launch the tool but I can't login to a oracle server with the tool

    Monday, September 23, 2013 9:18 PM
  • Also the win2k3 is 32bit
    Monday, September 23, 2013 9:18 PM
  • Can you verify that the oracle driver bin folder (containing oci.dll) is in the path?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 24, 2013 4:06 AM
  • Hi,

    Yes! I see oci.dll at the following directories:

    C:\app\oracle\product\11.2.0\client_1\

    C:\app\oracle\product\11.2.0\client_1\bin\

    C:\WINDOWS\system32\

    Tuesday, September 24, 2013 12:49 PM
  • Below link may helps you..

    https://forums.oracle.com/thread/2289794

    Tuesday, September 24, 2013 1:05 PM
  • OCI.dll should not be in c:\windows\system32.  What does the PATH look like?

    Is there a workstation you can run SSMA on?  It doesn't have to be run on the database server.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, September 24, 2013 1:52 PM
  • Hi, David and Pradeep_DBA:

    Thanks a lot for your link!

    I am actually using ODAC112021 to install oracle client fast run-time and odp.net only.

    I have removed oci.dll at C:\WINDOWS\system32\. De-installed all oracle and PLSQL developer. Afterwards, re-installed oracle using ODAC112021 and PLSQL developer.

    I copied all of dlls and exes at C:\app\oracle\product\11.2.0\client_1 directory into C:\app\oracle\product\11.2.0\client_1\bin directory too

    Here is the path for my win2k3 server:

    C:\app\oracle\product\11.2.0\client_1;C:\app\oracle\product\11.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\WINDOWS\system32\WindowsPowerShell\v1.0;c:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\TortoiseSVN\bin;C:\Program Files\Microsoft SQL Server Migration Assistant for Oracle\bin\

    I can use PLSQL developer to connect to my backend Oracle server, but I can't by Microsoft SQL Server Migration Assistant for Oracle. It is bad as we like to use sql server instead of oracle.

    I checked the link https://forums.oracle.com/thread/2289794, but it doesn't seem to help me



    Tuesday, September 24, 2013 2:02 PM
  • I also try to use window 7 to install SSMA and see what happens
    Tuesday, September 24, 2013 2:07 PM
  • Actually, I can not use my win7 because we are using VPN which is not supported on win7 yet! 
    Tuesday, September 24, 2013 2:10 PM
  • That looks right.  Now try launching SSMA as administrator to rule out permissions issues.

    But don't do this: "I copied all of dlls and exes at C:\app\oracle\product\11.2.0\client_1 directory into C:\app\oracle\product\11.2.0\client_1\bin directory too"

    Just leave them where the installer puts them and ensure that that folder is in the path.  You need to know which folder it's loading from because you may need to ensure the permissions are correct on that folder.  See eg:

    http://oraclepoint.com/oralife/2010/08/19/oracle-odbc-connection-issue-of-system-exception-ocienvcreate-failed-with-return-code-1-on-windows-with-asp/

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, September 24, 2013 2:29 PM
  • Hi, David:

    I finally make the progress that I find the problem and solve it. Thanks a lot for your effort!

    Tuesday, September 24, 2013 2:42 PM
  • What was the problem?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 24, 2013 2:57 PM
  • Hi,

    I’m glad to hear that you found a good idea to resolve your question.If you find out the root cause finally and share your solutions & experience here, it will be very beneficial for other community members who have similar questions.

    Thanks

    Candy Zhou

    Wednesday, September 25, 2013 9:44 AM