新手學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

評論 (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 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>