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

本文格式為Word版，下載可任意編輯——SQLServer鏈接服務(wù)器訪問OracleSQLServer鏈接服務(wù)器訪問Oracle

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

操作系統(tǒng)：WindowsServer2023R264位

數(shù)據(jù)庫版本：SQLServer2023R264位和OracleDatabase11g第2版(11.2.0.1.0)及相應(yīng)的客戶端版本：Oracle11gclient64位。

其中OracleDatabase11g安裝在一臺(tái)電腦，另一臺(tái)安裝SQLServer2023R264位和Oracle11gclient64位。

二、創(chuàng)立SQLServer鏈接服務(wù)

首先SQLServer鏈接Oracle可以通過兩個(gè)訪問接口：“MSDAORA〞和“OraOLEDB.Oracle〞。默認(rèn)狀態(tài)下，SQLServer2023R264位安裝后在服務(wù)器對象->鏈接服務(wù)器->訪問接口下并沒有“MSDAORA〞和“OraOLEDB.Oracle〞接口。

安裝“MSDAORA〞訪問接口需要下載OracleDataAccessComponents（ODAC）forWindows的相應(yīng)64位版本。安裝后重新啟動(dòng)服務(wù)器即可以看到“MSDAORA〞接口。由于此接口不支持分布式事務(wù)，因此不做過多的描述。

安裝“OraOLEDB.Oracle〞訪問接口需要下載Oracle11gclient64位。具體安裝過程如下：1）下載后解壓zip文件到文件夾，點(diǎn)擊setup.exe，彈出如下界面：

直接點(diǎn)[是]。

2）彈出如下界面：

選擇[定制]選項(xiàng)。點(diǎn)擊下一步，彈出如下界面：

直接點(diǎn)擊下一步，彈出如下界面：

選擇軟件安裝的位置后點(diǎn)擊下一步，彈出如下界面：

在可用產(chǎn)品組件窗口點(diǎn)擊[全選]后直接點(diǎn)擊下一步，彈出如下窗口：

在調(diào)度程序代理主機(jī)名中輸入本機(jī)的主機(jī)名。點(diǎn)擊下一步，彈出如下界面：

在OracleServicesforMTS中配置端口，這里直接使用默認(rèn)端口，點(diǎn)擊下一步：OracleServicesforMicrosoftTransactionServer

OracleServicesforMTS允許客戶在MTS協(xié)調(diào)事務(wù)中使用Oracle數(shù)據(jù)庫作為資源管理器，從而提供了Oracle解決方案和MTS之間的強(qiáng)大集成。OracleServicesforMTS用作Oracle數(shù)據(jù)庫到MTS分布式交易協(xié)同器的代理。因此，這些服務(wù)負(fù)責(zé)提供與MTS的客戶端連接合并，并允許COM組件通過Oracle參與MTS交易。此外，OracleServicesforMTS也可以操作運(yùn)行在任何操作系統(tǒng)上的Oracle數(shù)據(jù)庫（假使服務(wù)本身在Windows上運(yùn)行）。

直接點(diǎn)擊完成，開始安裝客戶端程序。3）配置注冊表

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

找到注冊表項(xiàng)HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSDTC\\MTxOCIOracleOciLib值改為oci.dll、OracleSqlLib值改為orasql11.dll、

OracleXaLib值改為oraclient11.dll。

HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\MSDTC\\MTxOCI對它做同樣的修改。

4）配置完成后重新啟動(dòng)計(jì)算機(jī)，繼續(xù)配置

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

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

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

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

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

輸入服務(wù)名（關(guān)于Oracle服務(wù)名與實(shí)例名，請參考Oracle服務(wù)名與實(shí)例名.doc），點(diǎn)擊下一步：

點(diǎn)擊測試中的[測試]按鈕，進(jìn)行服務(wù)器連接測試，注意要輸入正確的用戶名和密碼，可以直接點(diǎn)擊完成跳過測試步驟。

1、“MSDAORA〞訪問接口是由MicrosoftOLEDBProviderforOracle提供的，建議不使用此接口進(jìn)行鏈接。通過該接口建立的鏈接服務(wù)器在進(jìn)行查詢Oracle表時(shí)會(huì)報(bào)錯(cuò)，在帶數(shù)據(jù)類型CLOB、BLOB字段時(shí)。

錯(cuò)誤提醒如下：

“鏈接服務(wù)器\的OLEDB訪問接口\返回了消息\發(fā)生了一個(gè)Oracle錯(cuò)誤，但無法從Oracle中檢索錯(cuò)誤信息。\。鏈接服務(wù)器\的OLEDB訪問接口\返回了消息\數(shù)據(jù)類型不被支持。\

2、“OraOLEDB.Oracle〞訪問接口是由Oracle的OracleProviderforOLEDB驅(qū)動(dòng)提供的。它解決了兩個(gè)數(shù)據(jù)庫類型不一致的問題。而且假使需要使用分布式事務(wù)，必需使用它來創(chuàng)立鏈接服務(wù)器。

在創(chuàng)立之前，在SQLServer中，鏈接服務(wù)器->訪問接口->OraOLEDB.Oracle->右鍵屬性，選中“允許進(jìn)程內(nèi)〞。

這一步是使我們選擇的OraOLEDB.Oracle接口開啟執(zhí)行操作。如未設(shè)置會(huì)報(bào)如下錯(cuò)誤：“無法初始化鏈接服務(wù)器\的OLEDB訪問接口\的數(shù)據(jù)源對象\

使用OracleProviderforOLEDB驅(qū)動(dòng)創(chuàng)立sqlserver鏈接服務(wù)器的代碼：

--建立數(shù)據(jù)庫鏈接服務(wù)器

EXECSp_addlinkedserver

@server=N'ORACLEDB',--要?jiǎng)?chuàng)立的鏈接服務(wù)器別名@srvproduct=N'Oracle',--產(chǎn)品名稱

@provider=N'OraOLEDB.Oracle',--OLEDB驅(qū)動(dòng)名稱@datasrc=N'ORCL'--數(shù)據(jù)源

Oracle->ora11g->network->admin->tnsnames.ora查看

EXECSp_addlinkedsrvlogin'ORACLEDB',--已建立的鏈接服務(wù)器名'false',--固定*/

NULL,--為每個(gè)登陸SQLSERVER的用戶使用此鏈接服務(wù)器,則寫用戶名*/'scott',--帳號(hào)(Oracle)'tiger'--密碼

EXECSp_serveroption'ORACLEDB','rpcout','true'

EXECSp_serveroption'ORACLEDB','rpc','true'

--這兩個(gè)是開啟rpc,rpcout的，默認(rèn)為False,開啟后可以支持遠(yuǎn)程更改分布式事務(wù)。（如有分布式事務(wù)操作必需要設(shè)置）

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

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

查詢的兩種方式：

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

SELECT*FROM[ORACLEDB]..[SCOTT].[EMP]

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

2）使用OPENQUERY；

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

A.執(zhí)行SELECT傳遞查詢

SELECT*

FROMOPENQUERY(ORACLEDB,'SELECT*FROMSCOTT.EMP')

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

UPDATEOPENQUERY(ORACLEDB,'SELECTENAMEFROMSCOTT.EMPWHEREEMPNO=7369')SETENAME='SMITH';

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

INSERTOPENQUERY(ORACLEDB,'SELECT*FROMSCOTT.EMP')VALUES(8888,'JIAO','MANAGER',NULL,'1990-12-1700:00:00.0000000',5000.00,5000.00,10);

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

DELETEOPENQUERY(ORACLEDB,'SELECTENAMEFROMSCOTT.EMPWHEREEMPNO=8888');

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

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

啟用條件：

1、必需安裝OracleServicesforMicrosoftTransactionServer；

2、必需用OracleProviderforOLEDB驅(qū)動(dòng)提供的OraOLEDB.Oracle訪問接口來創(chuàng)立鏈接服務(wù)；3、必需雙方啟動(dòng)并配置MSDTC服務(wù)，關(guān)于MSDTC服務(wù)的配置請參考：SQLServer分布式事務(wù)配置.doc。

例如：

SETXACT_ABORTONBEGINTRA