跨伺服器存取 SQL Server：OPENROWSET 與連結伺服器完整指南

一、臨時查詢：OPENROWSET / OPENDATASOURCE

1. 查詢遠端 SQL Server

SELECT *
FROM OPENROWSET('SQLOLEDB',
    'RemoteServerName';'UserName';'Password',
    'DatabaseName.dbo.TableName');

2. 將遠端資料導入本地新表

SELECT * INTO LocalTable
FROM OPENROWSET('SQLOLEDB',
    'RemoteServerName';'UserName';'Password',
    'DatabaseName.dbo.TableName');

3. 查詢 Excel (Jet/ACE Provider)

SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Finance\account.xls";User ID=Admin;Password=;Extended Properties="Excel 5.0"'
)...[SheetName$];

二、長期連線：建立連結伺服器

-- 1. 建立連結伺服器
EXEC sp_addlinkedserver
    @server = 'SRV_LNK',
    @srvproduct = '',
    @provider = 'SQLOLEDB',
    @datasrc = 'RemoteServerName或IP';

-- 2. 設定登入對應 (使用特定帳號)
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'SRV_LNK',
    @useself = 'false',
    @locallogin = NULL,  -- 所有本地登入對應同一遠端帳號
    @rmtuser = 'RemoteUser',
    @rmtpassword = 'RemotePassword';

-- 3. 查詢遠端表 (四段式名稱)
SELECT * FROM SRV_LNK.DatabaseName.dbo.TableName;

-- 4. 導入遠端資料
SELECT * INTO LocalTable FROM SRV_LNK.DatabaseName.dbo.TableName;

-- 5. 不再使用時刪除
EXEC sp_dropserver 'SRV_LNK', 'droplogins';

三、權限與安全性設定

• 需啟用 Ad Hoc Distributed Queries：EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
• 連結伺服器預設只允許 sysadmin 使用；一般使用者需透過 sp_addlinkedsrvlogin 設定對應。
• 遠端伺服器需啟用「遠端存取」sp_configure 'remote access', 1 並允許 RPC。
• 建議使用 Windows 驗證 (Kerberos 委派) 避免密碼明文儲存；若用 SQL 驗證，請限制連結伺服器登入權限。

四、效能與最佳實踐

• 大量資料傳輸優先用 INSERT ... SELECT 推播至遠端，或用 SSIS/BCP/複寫。
• 跨伺服器 JOIN 效能差，盡量將遠端資料先拉至本地暫存表再 JOIN。
• 使用 OPENQUERY 將篩選條件推送到遠端執行：SELECT * FROM OPENQUERY(SRV_LNK, 'SELECT * FROM db.dbo.t WHERE col > 100')。
• 監控連結伺服器連線池與查詢耗時，設定適當的 query timeout 與 connection timeout。