在SQL Server2008 上建立Oracle 链接服务器

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
   1. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
   2. 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.
4. REBOOT!
5. Configure provider in SQL Server
   1. 

      OraOLEDB.Oracle Provider Menu Item

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

   3. 

6. Create a linked server to the Oracle Database
   1. 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
      5. 

         Create a Linked Server

   2. Security
      1. Select Be made using this security context and supply the remote login and password
7. Query the linked server：select * from openquery (TESTKM,'select * from DOC_SUBJECT')