oracle linked server from sql server 2008R2

Question

I want to create a linked server in SSMS to connect Oracle 11g(one of our service provider holds our data on oracle server and they are providing ODBC connection to that server)

I have SQL 2008R2 and I installed OraOLEDB.Oracle to create a linked server. I have tried to create linked server but it is giving me connection error. They provided me following TNSNAMES and connection string to be configured and user name and password. i really appreciate if some body can provide me step by step process to configure this connection correctly. Thanks in advance for your support.

abc8RG45 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST=206.xx.xx.xxx)(PORT=1630))

(ADDRESS = (PROTOCOL = TCP)(HOST=172.xx.xxx.xxx)(PORT=1524)))

(SOURCE_ROUTE=YES)

(CONNECT_DATA =

(SERVER=DEDICATED)

(SERVICE_NAME = GBA8PI33)

)

)

---

HAYAT

Answer 1

Hello,

Install Oracle Client 11.2 or later. Version 11.1 does not work.

Are you able to create an ODBC DSN and test the connection?  If you already created and tested the ODBC DSN, then use the following resources to create the linked server:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/bd9f8429-8c5e-4cce-85de-fd694eef6b53/#83cc5fad-c832-48d5-a211-51f91f3bdf35

http://www.sqlcoffee.com/Troubleshooting091.htm

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com