Add Oracle 10g as Linked Server

Question

Hi,
I want to add an oracle server as linked server in SQL Server 2005.

I have followed all the steps given in link : http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

1. Install Oracle Database 10g Client Release 2

   1. Install using the InstantClient option

2. Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)

   1. Select the Oracle Data Access Components option (not .NET!)
3. Edit TNSNAMES.ora
4. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
5. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):

   DMDEV =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = FRIENDLYNAME)
       )
     )

   There are a couple of things you need to change:

   • HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
   • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
6. REBOOT!
7. Configure provider in SQL Server
1. 

OraOLEDB.Oracle Provider Menu Item

 

8. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”

   

9. Create a linked server to the Oracle Database
10. General

     

    1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
    2. Provider: Oracle Provider for OLE DB
    3. Product Name: “Oracle” is fine here
    4. Data Source: This should match the HOST you defined in TNSNAMES.ora

        

       

       Create a Linked Server

11. Security
12. 1. Select Be made using this security context and supply the remote login and password

BUT I am still not able to query the tables of Oracle database.

This is the error I am getting.

Cannot create a an instance of OLEdb provider "OraOLEDB.oracle" for linked server "<<link server name>>"

Please help.......

Answer 1

What is the version of your SQL Server 2005, 32-bit or 64-bit? It it is 64-bit, please make sure that you have the matched 64-bit version Oracle client components installed.
Besides, please refer to this article to make a troubleshooting by yourself:
http://technet.microsoft.com/en-us/library/ms152516.aspx

Hope it helps.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help