在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

      OraOLEDB.Oracle Provider Menu Item

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

  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

        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')
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值