SQL Server 跨資料庫查詢實戰：連結伺服器 建立與應用全指南

打破資料庫孤島：Linked Server 實作詳解

企業級應用常面臨跨資料庫、跨伺服器查詢需求。SQL Server 提供連結伺服器機制，透過 OLE DB 提供者，讓本地實例能像操作本地表一樣存取遠端異構資料源（SQL Server、Oracle、MySQL、Excel 等）。

核心系統預存程序

1. sp_addlinkedserver — 建立連結伺服器定義

EXEC sp_addlinkedserver
    @server = 'LinkedSrvName',        -- 連結伺服器邏輯名稱
    @srvproduct = '',                 -- 產品名稱 (SQL Server 可留空)
    @provider = 'SQLOLEDB',           -- OLE DB Provider
    @datasrc = 'RemoteIP_or_Name';    -- 遠端伺服器位址

2. sp_addlinkedsrvlogin — 設定登入對應

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedSrvName',    -- 對應的連結伺服器
    @useself = 'false',               -- false: 使用指定帳密; true: 用當前登入憑證
    @locallogin = NULL,               -- 本地登入 (NULL 套用所有)
    @rmtuser = 'RemoteUser',          -- 遠端登入帳號
    @rmtpassword = 'RemotePwd';       -- 遠端密碼

權限需求： 需 `ALTER ANY LINKED SERVER` 權限；`sp_addlinkedsrvlogin` 需 `ALTER ANY LOGIN`。不可在使用者定義交易中執行。

完整建立範例

-- 1. 建立連結伺服器指向遠端 SQL Server
EXEC sp_addlinkedserver
    @server = 'ERP_Server',
    @provider = 'SQLNCLI',
    @datasrc = '192.168.10.50\ERPINST';

-- 2. 設定統一 SQL 認證登入
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'ERP_Server',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'erp_readonly',
    @rmtpassword = 'Str0ngP@ssw0rd';

跨庫查詢語法：四段式命名

SELECT * FROM [連結伺服器名].[資料庫名].[結構名].[表名];
-- 範例
SELECT * FROM [ERP_Server].[ERP_DB].[dbo].[Orders];

進階應用與注意事項

• 分散式交易： 需啟用 MSDTC (Distributed Transaction Coordinator)，確保跨伺服器 ACID。
• 效能考量： 遠端查詢涉及網路往返，盡量下推過濾條件（WHERE 子句）、只選必要欄位、避免大量資料跨網路傳輸。
• OpenQuery / OpenRowset： 適合複雜遠端邏輯，在遠端執行完再回傳結果，減少本地負擔。

  SELECT * FROM OPENQUERY(ERP_Server, 'SELECT OrderID, CustID FROM Orders WHERE OrderDate > ''2023-01-01''');

• 防火牆/埠： 確保 1433 (或自訂埠) 通訊暢通，必要時設定 `@provstr` 指定 `Network Library=DBMSSOCN` 強制 TCP/IP。
• 移除連結伺服器： `EXEC sp_dropserver 'LinkedSrvName', 'droplogins';`

常見錯誤排查