Results 1 to 7 of 7

Thread: ORA-12154: TNS:could not resolve the connect identifier specified.

  1. #1
    bmchan is offline Junior Member
    Join Date
    Jun 2009
    Posts
    5
    Rep Power
    0

    Default ORA-12154: TNS:could not resolve the connect identifier specified.

    I have client tools 10g release 2 for both 32 and 64 bit installed the destination is 10g release 2 64 bit. And I can use SQL Plus to connect to the defined TNS name from both home folders.

    I am running Windows Server 2003 x64 and SQL Server 2008 with the latest updates.

    When I try to create a new Oracle Connection using the Oracle Connection Manager Editor I get the error "Error at (Name) [Connection manger "(Name)"]: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified.

    I can TNSping the TNS service name, if I create an ODBC connection using the service name I can communicate with the Oracle environment. Because of the promise of better performance using the Attunity/Microsoft SSIS Oracle Connector I want to try to get it to work.

    Does anyone have any recommendations or advice?

    Thanks,
    Ben

  2. #2
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    6

    Default

    Hi Ben,

    At design time, when working with the SQL Server Business Intelligence Development Studio, the Oracle Connector is running in 32-bit mode and therefore it loads the 32-bit Oracle client.

    Most likelly, you are getting the error because the connector is loading the wrong Oracle client (oci.dll), where the TNS service is not defined.

    To solve the problem, you have two options:
    1. Specify the Oracle connection string, instead of the TNS service name in the Oracle Connection Manager Editor, for example, in the following format: host:port/service_name
    2. Define a dummy registry entry (Z_SSIS) as follows:
      1. Open the regedit utility.
      2. Locate the following Key:
        HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE.
      3. Right-Click on ORACLE node and click on New -> Key.
      4. Call the new key Z_SSIS (to make sure it's the last entry).
      5. Right-Click on the Z_SSIS node and click on New -> String.
      6. Name the property ORACLE_HOME.
      7. Double-Click on the ORACLE_HOME and set it to the location of the Oracle 32Bit installation home directory you whant to use.
    Regards,

    Gadi

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    bmchan is offline Junior Member
    Join Date
    Jun 2009
    Posts
    5
    Rep Power
    0

    Default Test Connection Failed ORA-06413

    Gadi,
    Thanks for the quick reply.

    So I first tried the first option you mentioned I changed the service name to the host:port/service but I get the error ORA-06413: Connection not open." when I test the connection. When I load up the Net Manager for the 32 bit oracle client folder I am able to test the same service I'm trying to connect to.

    I'm going to alter my registry as the second option describes. I'll post the result shortly.

    Thanks again,
    Benjamin

    Quote Originally Posted by Gadi.Farhat View Post
    Hi Ben,

    At design time, when working with the SQL Server Business Intelligence Development Studio, the Oracle Connector is running in 32-bit mode and therefore it loads the 32-bit Oracle client.

    Most likelly, you are getting the error because the connector is loading the wrong Oracle client (oci.dll), where the TNS service is not defined.

    To solve the problem, you have two options:
    1. Specify the Oracle connection string, instead of the TNS service name in the Oracle Connection Manager Editor, for example, in the following format: host:port/service_name
    2. Define a dummy registry entry (Z_SSIS) as follows:
      1. Open the regedit utility.
      2. Locate the following Key:
        HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE.
      3. Right-Click on ORACLE node and click on New -> Key.
      4. Call the new key Z_SSIS (to make sure it's the last entry).
      5. Right-Click on the Z_SSIS node and click on New -> String.
      6. Name the property ORACLE_HOME.
      7. Double-Click on the ORACLE_HOME and set it to the location of the Oracle 32Bit installation home directory you whant to use.
    Regards,

    Gadi

    Last edited by bmchan; 06-09-2009 at 11:13 AM. Reason: Disabling smiles in text

  4. #4
    bmchan is offline Junior Member
    Join Date
    Jun 2009
    Posts
    5
    Rep Power
    0

    Default Ora-12154

    Gadi,
    I edited the registry as instructed and then to be sure I rebooted the machine. When I go and try to test a new connection I get the same error. OCI error encountered. ORA-12154.

    Thanks,
    Ben

    Quote Originally Posted by Gadi.Farhat View Post
    Hi Ben,

    At design time, when working with the SQL Server Business Intelligence Development Studio, the Oracle Connector is running in 32-bit mode and therefore it loads the 32-bit Oracle client.

    Most likelly, you are getting the error because the connector is loading the wrong Oracle client (oci.dll), where the TNS service is not defined.

    To solve the problem, you have two options:
    1. Specify the Oracle connection string, instead of the TNS service name in the Oracle Connection Manager Editor, for example, in the following format: host:port/service_name
    2. Define a dummy registry entry (Z_SSIS) as follows:
      1. Open the regedit utility.
      2. Locate the following Key:
        HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE.
      3. Right-Click on ORACLE node and click on New -> Key.
      4. Call the new key Z_SSIS (to make sure it's the last entry).
      5. Right-Click on the Z_SSIS node and click on New -> String.
      6. Name the property ORACLE_HOME.
      7. Double-Click on the ORACLE_HOME and set it to the location of the Oracle 32Bit installation home directory you whant to use.
    Regards,

    Gadi


  5. #5
    bmchan is offline Junior Member
    Join Date
    Jun 2009
    Posts
    5
    Rep Power
    0

    Default Problem had something to do with the Oracle Client 10g release 2 and parentheses

    Found the problem here:
    Connecting to Oracle on 64-bit (x64) machine

    Had to apply patch:
    http://updates.oracle.com/ARULink/Pa...ch_num=5059238

    Did that and I could then reference by TNS name.

  6. #6
    bmchan is offline Junior Member
    Join Date
    Jun 2009
    Posts
    5
    Rep Power
    0

    Default Oracle hotfix was required.

    Had to apply this patch:


    Found others that had a similar issue here:
    Connecting to Oracle on 64-bit (x64) machine

  7. #7
    cpassuel is offline Junior Member
    Join Date
    Apr 2011
    Posts
    3
    Rep Power
    0

    Default

    You can also install a newer version of Oracle client.

    I had this problem with v10.2.0.1 and v10.2.0.3 solved it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •