Linked Server to Oracle data source Failing

Question

Linked Server to Oracle data source Failing

Denesh Naidoo

SSCrazy

Points: 2348
More actions
November 29, 2010 at 6:37 am

#227663

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 2

Hi Gianluca,

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 3

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 5

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

You're welcome.

I did nothing, you managed by yourself.

-- Gianluca Sartori