SQL Server 鏈接服務器訪問 Oracle

離線安裝 .NET Framework 3.5
方法一：使用 NetFx3.cab 文件
下載 NetFx3.cab 文件，并將其放置在 Windows 10 系統(tǒng)盤的 C:Windows 文件夾中。
以管理員身份運行命令提示符，輸入以下命令并回車：

dism /online /Enable-Feature /FeatureName:NetFx3 /Source:"%windir%" /LimitAccess

等待部署進度達到 100%，無報錯即可。
在“程序和功能”-“Windows 功能”中查看，確認 .NET Framework 3.5 選項已被勾選。

一、測試環(huán)境說明

操作系統(tǒng)：Windows 7 64bit ；Windows Server 2008 R2 64bit

數(shù)據(jù)庫版本：SQL Server 2008 R2 64 位 和 Oracle Database 11g 第 2 版 (11.2.0.1.0)

及相應的客戶端版本：Oracle 11g client 64 位。

二、創(chuàng)建 SQL Server 鏈接服務

首先 SQL Server 鏈接 Oracle 可以通過兩個訪問接口： “ MSDAORA ” 和“OraOLEDB.Oracle”。

默認狀態(tài)下，SQL Server 2008 R2 64 位安裝后在 服務器對象 -> 鏈接服務器 -> 訪問接口下并沒有 “OraOLEDB.Oracle”接口。

注意：在安裝之前記得關閉殺毒軟件：例如火絨，不然會出現(xiàn)：Oracle安裝出現(xiàn)CreateFile() error 32 when trying set file time 錯誤

第一步：安裝服務器版本 Oracle Database 11g參考文章

第二步：安裝“OraOLEDB.Oracle”訪問接口需要下載 Oracle 11g client 64 位。具體安裝過程如下：

1）下載后解壓 zip 文件到文件夾，點擊setup.exe，彈出如下界面：

直接點擊完成，開始安裝客戶端程序。

3）配置注冊表

打開運行，輸入 regedit ，按回車。

找到注冊表項 HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI

OracleOciLib 值改為 oci.dll、
OracleSqlLib 值改為 orasql11.dll、
OracleXaLib 值改為 oraclient11.dll。

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMSDTCMTxOCI 對它做同樣的修改。
4）配置完成后重新啟動計算機，繼續(xù)配置

在開始 -> 所有程序 -> Oracle - OraClient11g_home1 -> 配置和移植工具 下找到 Net Manager 點擊打開，彈出如下窗口：

點擊服務命名，選擇 編輯 –> 創(chuàng)建 彈出網(wǎng)絡服務名向?qū)?#xff1a;

在網(wǎng)絡服務名中輸入要連接的 Oracle 實例名。點擊下一步：

默認選擇[TCP/IP(Internet 協(xié)議)] 點擊下一步：

輸入要連接的主機名，端口號為 1521 ，Oracle 的默認端口，點擊下一步：

輸入服務名，點擊下一步：

點擊測試中的[測試]按鈕，進行服務器連接測試 ，注意要輸入正確的用戶名和密碼， 可以直接點擊完成跳過測試步驟。
注意：配置好net manager之后一定要關閉窗口進行保存，不然sqlserver鏈接服務器Oracle的時候會一直鏈接失敗

1、 “OraOLEDB.Oracle” 訪問接口是由 Oracle 的 Oracle Provider for OLE DB 驅(qū)動提供的。它解決了兩個數(shù)據(jù)庫類型不一致的問題。而且如果需要使用分布式事務，必須使用它來創(chuàng)建鏈接服務器。
在創(chuàng)建之前，在 SQL Server 中，鏈接服務器->訪問接口->OraOLEDB.Oracle->右鍵屬性，
選中“允許進程內(nèi)”。

這一步是使我們選擇的 OraOLEDB.Oracle 接口打開執(zhí)行操作。如未設置會報如下錯誤：

“無法初始化鏈接服務器 “null” 的 OLE DB 訪問接口 “OraOLEDB.Oracle” 的數(shù)據(jù)源對象"

或者使用 Oracle Provider for OLE DB 驅(qū)動創(chuàng)建 sqlserver 鏈接服務器的代碼：

--建立數(shù)據(jù)庫鏈接服務器 EXEC Sp_addlinkedserver
@server =N'ORCL',--要創(chuàng)建的鏈接服務器別名@srvproduct=N'Oracle',--產(chǎn)品名稱@provider=N'OraOLEDB.Oracle',-- OLE DB 驅(qū)動名稱@datasrc=N'ORCL' --數(shù)據(jù)源
Oracle->ora11g->network->admin->tnsnames.ora查看EXEC Sp_addlinkedsrvlogin 'ORACLEDB',--已建立的鏈接服務器名'false',-- 固定*/
NULL,--為每個登陸SQL SERVER的用戶使用此鏈接服務器,則寫用戶名*/
'YNMZDXWC',--帳號(Oracle) '1' --密碼
EXEC Sp_serveroption 'ORACLEDB',
'rpc out', 'true'EXEC Sp_serveroption'ORACLEDB',
'rpc', 'true'--這兩個是打開 rpc,rpc out 的，默認為 False,打開后可以支持遠程更改分布式事務。（如有分布式事務操作必須要設置）

這樣我們就建好了鏈接服務器，已經(jīng)可以通過它對Oracle 數(shù)據(jù)庫進行查詢。

三、鏈接服務器的查詢

查詢的兩種方式：

1）鏈接服務器別名…Oracle 名.oracle 表，注意使用大寫；

SELECT *FROM OPENQUERY(ZHANG,'SELECT *FROM ELE_DEPARTMENT') SELECT * FROM [ORACLEDB]..[SCOTT].[EMP]

這種方式可以進行操作，優(yōu)點是使用簡單，一目了然，而缺點是性能太差，查詢大數(shù)據(jù)量表很慢。效率太差。

2）使用 OPENQUERY；

對給定的鏈接服務器執(zhí)行指定的傳遞查詢。該服務器是 OLE DB 數(shù)據(jù)源。OPENQUERY 可以在查詢的 FROM 子句中引用，就象它是一個表名。OPENQUERY 也可以作為 INSERT、UPDATE 或 DELETE 語句的目標表進行引用。但這要取決于 OLE DB 訪問接口的功能。盡管查詢可能返回多個結果集，但是 OPENQUERY 只返回第一個。示例：
A.執(zhí)行 SELECT 傳遞查詢

SELECT *
FROM OPENQUERY(ORACLEDB, 'SELECT * FROM SCOTT.EMP') SELECT *
FROM OPENQUERY(ZHANG,'SELECT *FROM ELE_DEPARTMENT')

B.執(zhí)行 UPDATE 傳遞查詢

UPDATE OPENQUERY (ORACLEDB, 'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 7369')
SET ENAME = 'SMITH';

C.執(zhí)行 INSERT 傳遞查詢

INSERT OPENQUERY (ORACLEDB, 'SELECT * FROM SCOTT.EMP') VALUES (8888,'JIAO','MANAGER',NULL,'1990-12-17 00:00:00.0000000',5000.00,5000.00,10);

D.執(zhí)行 DELETE 傳遞查詢

DELETE OPENQUERY (ORACLEDB, 'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 8888');

查詢方式速度幾乎和在 Oralce 中一樣快。并且我們可以將 openquery() 當做表來用。

四、SQL Server 鏈接服務器實現(xiàn)分布式查詢

啟用條件：

1、 必須安裝 Oracle Services for Microsoft Transaction Server；

2、 必須用Oracle Provider for OLE DB 驅(qū)動提供的OraOLEDB.Oracle 訪問接口來創(chuàng)建鏈接服務；

3、 必須雙方啟動并配置 MSDTC 服務，關于 MSDTC 服務的配置請參考：SQLServer 分布式事務配置.doc。
示例：

SET XACT_ABORT ON BEGIN TRANUPDATE Openquery(ORACLEDB, 'SELECT * FROM EMP WHERE EMPNO = 7369') SET	COMM = 200
INSERT test_yiyanhua.dbo.EMP SELECT *
FROM	test_yiyanhua.dbo.EMP WHERE	EMPNO = 7499
IF @@ERROR <> 0 ROLLBACK TRAN
ELSE
COMMIT TRAN

五、徹底卸載Oracle 11g服務端

徹底卸載Oracle 11g服務端

六、相關Oracle軟件安裝包下載地址

相關Oracle軟件安裝包下載地址
提取碼：sZZA