SQL Server 鏈接服務(wù)器訪問 Oracle

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

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

等待部署進(jìn)度達(dá)到 100%，無報(bào)錯(cuò)即可。
在“程序和功能”-“Windows 功能”中查看，確認(rèn) .NET Framework 3.5 選項(xiàng)已被勾選。

一、測試環(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)

及相應(yīng)的客戶端版本：Oracle 11g client 64 位。

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

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

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

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

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

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

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

直接點(diǎn)擊完成，開始安裝客戶端程序。

3）配置注冊表

打開運(yùn)行，輸入 regedit ，按回車。

找到注冊表項(xiàng) HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI

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

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

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

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

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

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

輸入要連接的主機(jī)名，端口號為 1521 ，Oracle 的默認(rèn)端口，點(diǎn)擊下一步：

輸入服務(wù)名，點(diǎn)擊下一步：

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

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

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

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

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

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

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

三、鏈接服務(wù)器的查詢

查詢的兩種方式：

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

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

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

2）使用 OPENQUERY；

對給定的鏈接服務(wù)器執(zhí)行指定的傳遞查詢。該服務(wù)器是 OLE DB 數(shù)據(jù)源。OPENQUERY 可以在查詢的 FROM 子句中引用，就象它是一個(gè)表名。OPENQUERY 也可以作為 INSERT、UPDATE 或 DELETE 語句的目標(biāo)表進(jìn)行引用。但這要取決于 OLE DB 訪問接口的功能。盡管查詢可能返回多個(gè)結(jié)果集，但是 OPENQUERY 只返回第一個(gè)。示例：
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() 當(dāng)做表來用。

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

啟用條件：

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

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

3、 必須雙方啟動(dòng)并配置 MSDTC 服務(wù)，關(guān)于 MSDTC 服務(wù)的配置請參考：SQLServer 分布式事務(wù)配置.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服務(wù)端

徹底卸載Oracle 11g服務(wù)端

六、相關(guān)Oracle軟件安裝包下載地址

相關(guān)Oracle軟件安裝包下載地址
提取碼：sZZA