SQL Server 透過 OPENROWSET/OPENQUERY 使用 Pass-Through 查詢存取異構資料源

Pass-Through 查詢概念

SQL Server 將查詢字串原樣傳送至 OLE DB 提供者，由遠端引擎解析執行，結果以資料列集返回。適用於：

• 異構資料庫 (Oracle、DB2、MySQL、PostgreSQL、Access、Excel、文字檔)
• 需使用遠端原生語法 (PL/SQL、T-SQL 方言差異)
• 效能考量：在遠端完成過濾/聯結，僅傳回結果集

兩種行集來源

1. 目錄物件：資料表、檢視、同義詞 — 所有提供者支援
2. 命令執行結果：SQL/指令碼 — 需提供者支援 ICommand 介面

三大 Pass-Through 函數

實戰範例：存取 Access Northwind

SELECT *
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',           -- Provider
    'C:\Northwind.mdb';'admin';'',       -- Connection string (Data Source;User ID;Password)
    'SELECT CustomerID, CompanyName      -- Pass-through query (Jet SQL)
     FROM Customers
     WHERE Region = ''WA'''
);

注意：Pass-through 查詢內部的單引號需雙寫 (''WA'')。

連結伺服器最佳實務

1. 建立連結伺服器 (一次設定，多次使用)：

   EXEC sp_addlinkedserver 
       @server = 'ORA_PROD',
       @srvproduct = 'Oracle',
       @provider = 'OraOLEDB.Oracle',
       @datasrc = 'ORCL';  -- TNS alias
   
   EXEC sp_addlinkedsrvlogin 
       @rmtsrvname = 'ORA_PROD',
       @useself = 'FALSE',
       @locallogin = NULL,
       @rmtuser = 'app_user',
       @rmtpassword = '*****';
   

2. 使用 OPENQUERY 執行 Oracle 原生 SQL：

   SELECT *
   FROM OPENQUERY(ORA_PROD, '
       SELECT customer_id, customer_name, order_total
       FROM orders
       WHERE order_date BETWEEN DATE ''2024-01-01'' AND DATE ''2024-01-31''
         AND status = ''SHIPPED''
   ');
   

3. 效能優化**：
   • 在遠端完成過濾、聯結、彙總，避免搬運大量原始資料
   • 確保遠端欄位有索引支援 WHERE/JOIN 條件
   • 使用 SET STATISTICS IO, TIME ON 比較 OPENQUERY vs 四部分命名 (LINKED..SCHEMA.TABLE) 效能

提供者選擇指南

安全性與權限

• Ad Hoc Distributed Queries 伺服器選項需啟用 (預設關閉)：EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE
• 僅授予 sysadmin 或具備 CONTROL SERVER 權限的登入使用 OPENROWSET/OPENDATASOURCE
• 一般應用程式改用連結伺服器 + OPENQUERY，由 DBA 控制連線字串與認證
• 啟用 SSL/TLS 加密 連結伺服器連線 (Oracle: SSL=TRUE、SQL Server: Encrypt=Yes)

常見錯誤排查

結語

Pass-Through 查詢是 SQL Server 整合異構資料的利器。核心原則：在資料所在地處理資料，僅傳輸結果集。生產環境請優先使用連結伺服器 + OPENQUERY，集中管理連線與認證，配合提供者原生語法發揮遠端引擎最大效能。