您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

Access 資料匯入 SQL Server 2005：OPENDATASOURCE 實戰腳本

編輯：關於SqlServer

核心挑戰：分散式查詢預設停用

SQL Server 2005 預設關閉 `Ad Hoc Distributed Queries`，直接執行 `OPENDATASOURCE`/`OPENROWSET` 會報錯。需先啟用進階選項。

完整啟用與匯入腳本

-- 1. 啟用進階選項與分散式查詢
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

-- 2. 匯入資料 (範例：Access 2000/2003 .mdb)
-- 參數說明：
-- 'Microsoft.Jet.OLEDB.4.0' : Access 2000-2003 Provider
-- 'Data Source="C:\Path\DB.mdb"' : 檔案完整路徑 (雙引號處理空格)
-- 'Jet OLEDB:Database Password=YourPwd' : 資料庫密碼 (無密碼可省略)
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\Source.mdb";Jet OLEDB:Database Password=Secret'
)...[SourceTableName];  -- 注意：三段式命名 ...[Table] 或 ...dbo.[Table]

GO

-- 3. (建議) 操作完成後關閉分散式查詢減少攻擊面
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

關鍵細節避坑指南

問題	解決方案
Access 2007+ (.accdb) 格式	Provider 改為 `'Microsoft.ACE.OLEDB.12.0'` (需安裝 Access Database Engine 2010/2016 Redistributable，位元數需與 SQL Server 服務一致，通常 64 位元)
路徑含空格/特殊字元	`Data Source` 值用雙引號包裹：`'Data Source="C:\My Data\DB.mdb"'`
64 位元 SQL Server 找不到 Jet Provider	Jet 只有 32 位元。必須安裝 64 位元 ACE Provider，並確保 SQL Server 服務為 64 位元。或使用 32 位元 SQL Server / SSIS 32 位元執行模式。
欄位名稱保留字/空格	Access 查詢中用方括號：`SELECT [Order Date], [Group] FROM ...`
大量資料效能	啟用 `TABLOCK` 提示：`INSERT INTO TargetTable WITH (TABLOCK) ...`；考慮 `BCP`/`SSIS` 替代。
資料類型不匹配	Access `Memo` → SQL `NVARCHAR(MAX)`；`OLE Object` → `VARBINARY(MAX)`；`Yes/No` → `BIT`。建議先建立目標表再匯入。