Linked Server to Oracle data source Failing

  • Hi All,

    Working with SQL Server 2008 R2. I've created a Linked server to an Oracle source, but when I right click on the Linked Server name and test it, it fails with the following:

    Cannot initialize the data source "OraOLEDB.Oracle" for linked server "LinkedServername"

    OLE.DB provider "OraOLEDB.Oracle" for linked server "LinkedServername" retunred message "ORA-12154:TNS:Could not resolve the connect identifier specified".

    I've successfully created the System DSN in ODBC Data Source Administrator, and when I test the data source from there it completes successfully.

    Thanks

    Denesh

  • You have to configure your oracle client using a file named "tnsnames.ora" that have to be placed under network\admin in your client directory.

    The file should contain something similar to this:

    your_server_alias =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = your_server_name)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = service_name_or_sid)

    )

    )

    For instance:

    HR_DB =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.internal.mynetwork)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = HR)

    )

    )

    I know, it sucks.

    -- Gianluca Sartori

  • Hi Gianluca,

    Apologies for that...wasn't sure if I'd posted in the right forum

  • Hi Gianluca,

    Thanks for the reply.

    I have done that as well and that is what is confusing me. I've created a System DSN which tests succesfully. From what I understand, the DSN makes use of the TNSNAMES.ORA file

    So if the DSN tests successfully, not sure why the Linked Server is failing with the TNS error

  • Are you using the exact same alias in tnsnames.ora and in the linked server?

    I'm not sure, but it could be case-sensitive.

    -- Gianluca Sartori

  • Hi Gianluca,

    The names I was using for the Data Source and the Product Name in the Linked server were not correct. It's now working!.

    Thank you for your help

  • You're welcome.

    I did nothing, you managed by yourself.

    -- Gianluca Sartori

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply