SQL Server connect to oracle 11g via linkedserver

SQL Server connect to oracle 11g via linkedserver

env: Windows Server 2012
        SQL Server 2012
        Oracle Enterprise Linux 6
        Oracle 11g


1.下載odac(xcopy edition)到SQL Server主機

2.建立以下路徑
1).c:\oracle\odac64
2).c:\oracle\odac64\bin

3.設定tnsnames.ora
1).建立以下路徑，C:\oracle\odac64\network\admin
2).建立檔案tnsnames.ora
3).在檔案tnsnames.ora輸入以下內容

服務名稱可以自訂，ip請以自己的Oracle主機為主。
服務名稱一:fa0test ip:100.168.114.120
服務名稱二:fa0prd01 ip:192.168.125.214

範本:
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
fa0test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 100.168.114.120 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fa0test)
    )
  )
fa0prd01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.214 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fa0prd01)
    )
  )

 4.設定Windows環境變數
 請以自己的環境內容為主，並做調整

調整前環境變數內容:
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\IBM\Client Access\Emulator;C:\Program Files (x86)\IBM\Client Access\Shared;C:\Program Files (x86)\IBM\Client Access\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

調整後環境變數內容:
增加此變數"c:\oracle\odac64;c:\oracle\odac64\bin;"



c:\oracle\odac64;c:\oracle\odac64\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\IBM\Client Access\Emulator;C:\Program Files (x86)\IBM\Client Access\Shared;C:\Program Files (x86)\IBM\Client Access\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

5.Reboot Server

6.確認設定是否成功
在llinkedserver要有OraOLEDB.Oracle





















7.設定OraOLEDB.Oracle
保留"允許 Inprocess"

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO













8.設定Linkedserver
輸入linkedserver name
選擇"Oracle Provider for OLE DB"
並輸入TNSNAMES裡面設定的服務名稱

9.使用OPENQUERY對Oracle的表進行查詢。

SELECT * FROM OPENQUERY(FA0TEST,'SELECT * FROM TFP1U1.TB_M1_COUNT')