前言
本文將討論三項主題，分別是使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器(Linked Server) 來與 Oracle 執行個體連接的設定與相關注意事項。
示範環境：
1. Windows Server 2008 R2 x64 + SQL Server 2008 R2 SP1 x64 版本。
2. Oracle 11g R2 x64 版本。
準備工作：
1. 請先安裝與組態好 Oracle Client x64 版本，並確認可以連接到此 Oracle 執行個體。
2. 重新啟動 SQL Server 服務。
3. 先啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項，請參考以下的文章：
SQL Server 2008 介面區組態(Surface Area Configuration，SAC)：
啟用 Ad Hoc Distributed Queries。訊息 15281，SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。
http://sharedderrick.blogspot.com/2011/02/sql-server-2008-surface-area.html
(一) 使用 OPENROWSET 資料表函數
包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。
這個方法是存取連結伺服器資料表的另一個方法，而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。
對於更常用到的 OLE DB 資料來源參考，請改用連結的伺服器。您可以依照資料表名稱的相同方式，在查詢的 FROM 子句中參考 OPENROWSET 函數。
根據 OLE DB 提供者的能力而定，OPENROWSET 函數也可以被當做 INSERT、UPDATE 或 DELETE 陳述式的目標資料表加以參考。
雖然查詢可以傳回多個結果集，但是 OPENROWSET 只能傳回第一個。
語法：
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
</bulk_options>
範例程式碼：
-- 01_使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT SYSDATE FROM dual')
圖1：使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期
-- 02_查詢 Dynamic Performance (V$) Views：v$version 取得 Oracle 執行個體的相關版本資訊
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM v$version')
圖2：取得 Oracle 執行個體的相關版本資訊
-- 03_若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM HR.DEPARTMENTS')
圖3：若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
(二) 使用 OPENDATASOURCE 函數
提供特定連接資訊做為四部分物件名稱，而不使用連結伺服器名稱。
語法：
OPENDATASOURCE ( provider_name, init_string )
範例程式碼：
-- 01_若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
SELECT * FROM OPENDATASOURCE
('OraOLEDB.Oracle','Data Source=test01; User ID=SYSTEM; Password=Pa$$w0rd')
..HR.DEPARTMENTS
圖 4：若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
(三)實作練習：
使用連結伺服器(Linked Server) 連接到 Oracle；以 SQL Server 2008 R2 與 Oracle 11g R2 為例
準備工作：
1. 請先安裝與組態好 Oracle Client x64 版本，並確認可以連接到此 Oracle 執行個體。
2. 重新啟動 SQL Server 服務。
工作1：檢視 SQL Server 上是否有偵測到 Oracle Client 資料提供者
步驟01. 使用 SSMS 管理工具，連接到目標的伺服器。
步驟02. 展開「伺服器物件」\「連接的伺服器」，展開「提供者」節點：
圖1：尚未安裝 Oracle Client
圖2：已經安裝 Oracle Client
在上圖 2 中，可以看到已經增加了 OraOLEDB.Oracle
步驟03. 點選此「OraOLEDB.Oracle」提供者，滑鼠右鍵，選擇「屬性」。
圖3：檢視「OraOLEDB.Oracle」提供者的屬性
工作2：建立連結伺服器
步驟01. 使用 sp_addlinkedserver 建立「連結伺服器」：
建立連結伺服器。連結伺服器可讓您對 OLE DB 資料來源存取分散式異質性查詢。
當您使用 sp_addlinkedserver 建立連結伺服器之後，即可對這部伺服器執行分散式查詢。
如果連結伺服器被定義為 SQL Server 的執行個體，就可以執行遠端預存程序。
-- 建立連結伺服器
語法：
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
-- 範例：
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = N'Oracle_test01')
EXEC master.dbo.sp_dropserver @server=N'Oracle_test01', @droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@server ='Oracle_test01', @srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle', @datasrc = 'test01 '
GO
圖4：檢視Oracle連結伺服器一般頁籤
圖5：檢視Oracle連結伺服器的安全性頁籤
在上圖 5 中，可以觀察到在「安全性」頁籤，目前使用的設定值是：「使用登入的目前安全性內容建立」。
圖6：檢視Oracle連結伺服器的伺服器選項頁籤
步驟02. 使用 sp_addlinkedsrvlogin 建立與遠端伺服器的安全性帳戶之對應關係：
建立或更新 SQL Server 本機執行個體登入與遠端伺服器安全性帳戶之間的對應。
-- 語法：
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
-- 範例：
EXEC sp_addlinkedsrvlogin
@rmtsrvname ='Oracle_test01', @useself ='FALSE',
@rmtuser ='SYSTEM', @rmtpassword = 'Pa$$w0rd'
GO
圖7：檢視Oracle連結伺服器的安全性頁籤
在上圖 7 中，可以觀察到目前使用的設定值是：「使用此安全性內容建立」，並且有設定「遠端登入」與「指定密碼」。
步驟03. 點選先前所建立的「連結伺服器」：Oracle_test01，滑鼠右鍵，選擇「測試連結」。
圖8：點選「測試連接」
步驟04. 在「連結的伺服器」視窗，點選「確定」。
圖9：連接測試_成功
若是輸入的帳戶或是密碼不正確時，所遭遇的錯誤訊息如下：
與連結伺服器的連接測試失敗。
無法初始化連結伺服器 "Oracle_test01" 的 OLE DB 提供者 "OraOLEDB.Oracle" 的資料來源物件。
連結伺服器 "Oracle_test01" 的 OLE DB 提供者 "OraOLEDB.Oracle" 傳回訊息 "ORA-01017: invalid username/password; logon denied"。
(Microsoft SQL Server, 錯誤: 7303)
如需說明，請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
圖10：若帳戶或密碼不正確時的錯誤訊息
步驟04. 調整「連結伺服器」的安全性，請參考以下的範例程式碼：
-- 設定：指定的 Windows 帳戶與遠端伺服器的安全性帳戶之對應關係
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = N'SQL2K8R2BI\Administrator',
@useself = N'False', @rmtuser = N'SYSTEM', @rmtpassword = N'Pa$$w0rd'
GO
-- 設定：指定的 SQL 帳戶與遠端伺服器的安全性帳戶之對應關係
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = N'sa',
@useself = N'False', @rmtuser = N'SYSTEM', @rmtpassword = N'Pa$$w0rd'
GO
-- 使用 sp_droplinkedsrvlogin 移除在執行 SQL Server 之本機伺服器的登入與連結伺服器的登入之間的現有對應。
EXEC master.dbo.sp_droplinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = NULL
GO
圖11：設定本機伺服器登入與遠端伺服器登入對應
在上圖 11 中，可以觀察到設定的是「不建立」。
僅能使用指定的 Windows 帳戶與 SQL 帳戶來登入本機伺服器後，才能連接到遠端的伺服器。
步驟05. 測試與「連結伺服器」，執行以下的範例程式碼：
圖12：SYSDATA 函數查詢 Oracle 執行個體上的系統日期
-- OPENQUERY 函數：查詢 Dynamic Performance (V$) Views：v$version 取得 Oracle 執行個體的相關版本資訊
SELECT * FROM OPENQUERY(Oracle_test01,'SELECT * FROM v$version')
圖13：Oracle 執行個體的相關版本資訊
-- 若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
SELECT * FROM Oracle_test01..HR.DEPARTMENTS
-- OPENQUERY 函數：若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
SELECT * FROM OPENQUERY(Oracle_test01,'SELECT * FROM HR.DEPARTMENTS')
圖14：查詢範例資料表HR.DEPARTMENTS
若是使用 SSMS 管理工具來組態「連結伺服器」與 Oracle 執行個體連線，可能會遭遇到以下的錯誤訊息：
執行 Transact-SQL 陳述式或批次時發生例外狀況。 (Microsoft.SqlServer.ConnectionInfo)
不允許對系統目錄的特定更新。 (Microsoft SQL Server, 錯誤: 259)
如需說明，請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=259&LinkId=20476
圖15：不允許對系統目錄的特定更新之錯誤訊息
不知道為何使用 SSMS 管理工具執行設定「連結伺服器」的組態設定時，
SSMS 管理工具會自動產生以下修改系統資料表：[master].[dbo].[sysservers] 的範例程式碼範例程式碼：
update [master].[dbo].[sysservers] set [datasource] = 'test01' where [srvname] = 'Oracle_test01'
GO
--
訊息 259，層級 16，狀態 1，行 1
不允許對系統目錄的特定更新。
--
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
圖16：訊息259_不允許對系統目錄的特定更新
這功能是用來更新系統資料表的相關資料，但系統內定是不得更新系統資料表。
可能的解決方案：
仍是可以使用 SSMS 管理工具來組態。
但是，不按下「確定」，而是改以產生「指令碼」的方式後。
再自行刪除上述修改系統資料表：[master].[dbo].[sysservers] 的範例程式碼，就可以正常的執行後續的指令碼。
結語
在本期中，介紹了使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器(Linked Server) 來與 Oracle 執行個體連接的設定與相關注意事項。
訂閱