SQL Server 透過 OPENROWSET 與 OPENQUERY 使用直接傳遞查詢

SQL Server 2000 提供了強大的分布式查詢能力，允許將 OLE DB 數據源（如 Access、Oracle、Excel、文本文件）視為虛擬表直接參與 T-SQL 查詢。核心在於 OPENROWSET、OPENQUERY 與 OPENDATASOURCE 三大函數，它們讓跨數據源查詢如同操作本地表般自然。本文將深入解析其原理、語法與實戰場景。

核心概念：直接傳遞查詢

直接傳遞查詢將未經解析的命令字串直接發送至 OLE DB 提供程序執行。查詢語法必須符合目標數據源規範（如 Access 用 Jet SQL，Oracle 用 PL/SQL）。SQL Server 僅負責接收結果集並以表格形式呈現，可直接用於 FROM、JOIN、INSERT 等子句中。

三大核心函數對比

實戰範例：從 Access Northwind 數據庫查詢數據

SELECT *
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Northwind.mdb'; 'admin'; '',
    'SELECT CustomerID, CompanyName FROM Customers WHERE Region = ''WA''');

參數解析：

1. 'Microsoft.Jet.OLEDB.4.0'：Access 2000/OLE DB Provider 名稱。
2. 'C:\Northwind.mdb'; 'admin'; ''：連接字串（數據源路徑、用戶名、密碼）。
3. 'SELECT ...'：傳遞給 Access 引擎執行的原生 SQL（注意單引號轉義）。

進階用法與最佳實踐

1. 將結果集作為表參與 JOIN 查詢

SELECT e.EmployeeName, o.OrderID, o.OrderDate
FROM LocalEmployees e
INNER JOIN OPENROWSET(
    'SQLOLEDB', 'Server=RemoteSrv;UID=sa;PWD=xxx',
    'SELECT OrderID, OrderDate, EmpID FROM Northwind.dbo.Orders') o
ON e.EmpID = o.EmpID;

2. 直接插入遠程數據

INSERT INTO LocalArchive (OrderID, OrderDate, CustomerID)
SELECT OrderID, OrderDate, CustomerID
FROM OPENROWSET('SQLOLEDB', 'Server=Prod;UID=etl;PWD=xxx',
                'SELECT OrderID, OrderDate, CustomerID FROM Orders WHERE OrderDate < ''2020-01-01''');

3. 使用鏈接服務器簡化管理

-- 一次性創建鏈接服務器
EXEC sp_addlinkedserver @server = 'AccessNorthwind',
    @srvproduct = '',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Northwind.mdb';

-- 後續查詢極度簡潔
SELECT * FROM OPENQUERY(AccessNorthwind, 'SELECT * FROM Customers WHERE Country = ''USA''');

提供程序支援與限制

• 所有 OLE DB 提供程序 均支援引用可表現為表格行集的對象（表、視圖）。
• 支援 Command 對象 的提供程序（如 SQLOLEDB、Jet OLEDB、MSDAORA）才能執行命令字串（即直接傳遞查詢）。
• OLE DB 規範未統一命令語言：關係型數據庫通常支援 SQL，非關係型源（郵件、目錄服務）則使用專屬語言。
• Access/Excel 文件路徑必須為 SQL Server 服務賬戶可訪問的本地路徑或 UNC 路徑，映射網絡盤符不可靠。

常見問題排查

總結

直接傳遞查詢打破了數據孤島，讓 SQL Server 成為企業級數據整合樞紐。掌握 OPENROWSET（臨時）、OPENQUERY（常規）、OPENDATASOURCE（輕量）三者的適用邊界，並善用鏈接服務器管理連接元數據，能極大提升跨源查詢的開發效率與運行穩定性。記住：遠程邏輯下推、類型顯式轉換、認證提前規劃 是生產環境落地的三大鐵律。

1. 上一篇文章： SQL Server INSERT INTO SELECT 語法詳解與避坑指南
2. 下一篇文章： SQL Server REPLACE 函數完整指南：語法、參數與實戰範例

關於SqlServer

遠端連線 SQL Server 完整圖解教學（三）：選擇驗證模式、輸入登入憑證

註冊精靈已進入驗證設定階段，需指定連線遠端 SQL Serv

SQL Server 2008 記錄傳送完整實作指南：備份→複製→還原三步驟自動化

什麼是記錄傳送？ 核心三動作：**備份 → 複製 → 還原*

SQL Server 資料遷移至 Access 兩步走：ADO Recordset 循環寫入實現

場景描述 來源：SQL Server 資料表

SQL Server 2008/2012 資料庫降級遷移至 2005：產生腳本法完整步驟

特殊需求：將高版本 SQL Server (2008/201