新手學SQL Server 2008使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器來連接 Oracle 資料庫

by Derrick 19. 三月 2012 14:00

前言

本文將討論三項主題，分別是使用 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')

01_使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期

圖1：使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期

 

-- 02_查詢 Dynamic Performance (V$) Views：v$version 取得 Oracle 執行個體的相關版本資訊
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM v$version')

02_取得 Oracle 執行個體的相關版本資訊

圖2：取得 Oracle 執行個體的相關版本資訊

 

-- 03_若是有安裝 Oracle 範例資料庫，可以查詢資料表：HR.DEPARTMENTS
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM HR.DEPARTMENTS')

03_若是有安裝 Oracle 範例資料庫，可以查詢資料表：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

04_若是有安裝 Oracle 範例資料庫，可以查詢資料表：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. 展開「伺服器物件」\「連接的伺服器」，展開「提供者」節點：

01_尚未安裝 Oracle Client

圖1：尚未安裝 Oracle Client

02_已經安裝 Oracle Client

圖2：已經安裝 Oracle Client

在上圖 2 中，可以看到已經增加了 OraOLEDB.Oracle


步驟03. 點選此「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

04_檢視Oracle連結伺服器一般頁籤

圖4：檢視Oracle連結伺服器一般頁籤

05_檢視Oracle連結伺服器的安全性頁籤

圖5：檢視Oracle連結伺服器的安全性頁籤

在上圖 5 中，可以觀察到在「安全性」頁籤，目前使用的設定值是：「使用登入的目前安全性內容建立」。

06_檢視Oracle連結伺服器的伺服器選項頁籤

圖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

07_檢視Oracle連結伺服器的安全性頁籤

圖7：檢視Oracle連結伺服器的安全性頁籤

在上圖 7 中，可以觀察到目前使用的設定值是：「使用此安全性內容建立」，並且有設定「遠端登入」與「指定密碼」。


步驟03. 點選先前所建立的「連結伺服器」：Oracle_test01，滑鼠右鍵，選擇「測試連結」。

08_點選「測試連接」

圖8：點選「測試連接」

步驟04. 在「連結的伺服器」視窗，點選「確定」。

09_連接測試_成功

圖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_若帳戶或密碼不正確時的錯誤訊息

圖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：設定本機伺服器登入與遠端伺服器登入對應

在上圖 11 中，可以觀察到設定的是「不建立」。
僅能使用指定的 Windows 帳戶與 SQL 帳戶來登入本機伺服器後，才能連接到遠端的伺服器。


步驟05. 測試與「連結伺服器」，執行以下的範例程式碼：

12_SYSDATA 函數查詢 Oracle 執行個體上的系統日期

圖12：SYSDATA 函數查詢 Oracle 執行個體上的系統日期

-- OPENQUERY 函數：查詢 Dynamic Performance (V$) Views：v$version 取得 Oracle 執行個體的相關版本資訊
SELECT * FROM OPENQUERY(Oracle_test01,'SELECT * FROM v$version')

13_Oracle 執行個體的相關版本資訊

圖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

圖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_不允許對系統目錄的特定更新之錯誤訊息

圖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_不允許對系統目錄的特定更新

圖16：訊息259_不允許對系統目錄的特定更新

這功能是用來更新系統資料表的相關資料，但系統內定是不得更新系統資料表。

 

可能的解決方案：


仍是可以使用 SSMS 管理工具來組態。
但是，不按下「確定」，而是改以產生「指令碼」的方式後。

再自行刪除上述修改系統資料表：[master].[dbo].[sysservers] 的範例程式碼，就可以正常的執行後續的指令碼。

結語

在本期中，介紹了使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器(Linked Server) 來與 Oracle 執行個體連接的設定與相關注意事項。

Tags:

Oracle資料庫 | SQL Server資料庫 | 陳俊宇Derrick Chen

Permalink | 評論 (3)

評論 (3) -

JERRY
JERRY Taiwan
2012/10/16 下午 03:16:15 #

謝謝大大的分享，有效地解決了我的問題。 Thanks!

回覆

Charles Chang
Charles Chang Taiwan
2013/8/1 下午 06:36:13 #

新增的 Linked Server 如果也是 SQL Server, 可以用:
sp_addlinkedserver @server='TEST01',@srvproduct='SQLNCLI',@provider='SQLNCLI'
,@provstr='UID=sa;pwd=XXXX;Persist Security Info=true;Data Source=127.0.0.1;Initial Catalog=testdb'

其中,也可以拿掉 Initial Catalog=testdb

查詢語法為  select top 100 *  from TEST01.testdb.dbo.[TableName]; -- TableName 就是資料庫裏的資料表名稱

回覆

Charles Chang
Charles Chang Taiwan
2013/8/1 下午 06:59:46 #

新增的 Linked Server 如果也是 SQL Server, 二段式新增(先新增  Linked Server , 再指定 login user)可以用:
EXEC master.dbo.sp_addlinkedserver @server = N'TEST02', @srvproduct=N'SQLOLEDB', @provider=N'SQLNCLI', @datasrc=N'127.0.0.1\sqlexpress';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST02',@useself=N'False',@rmtuser=N'sa',@rmtpassword='interfly' ;
--  @useself='False'  是指不直接用原登入之帳號、密碼去模擬/冒充(impersonate) 登入  Linked Server
-- 而是以指定之帳號(rmtuser)、密碼(rmtpassword)去登入  Linked Server

查詢語法為  select top 100 *  from TEST02.testdb.dbo.[TableName]; -- TableName 就是資料庫裏的資料表名稱

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌，由恆逸資訊創立於2000，自發刊日起迄今已發行超過500篇.NET相關技術文章，擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享，歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌，讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>