通过 Microsoft.Ace.OLEDB 接口导入 EXCEL 到SQLSERVER

使用前确保安装 AccessDatabaseEngine_X64.exe

本机环境：

win7 x64 SP1 旗舰版

SqlServer 2008 R2

（注：

SqlServer 实例一定要是和AccessDatabaseEngine位数相匹配，系统是x64数据库也需要是x64，查看方法为使用数据库SQL：SELECT @@VERSION
32位：Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)   Jun 17 2011 00:57:23   Copyright (c) Microsoft Corporation  Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64) 

64位：Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ）

Office 2010 32位版

使用过程中遇到过的异常：

无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Ace.OLEDB.12.0" 的实例。

因为 OLE DB 访问接口 'Microsoft.Ace.OLEDB.12.0' 配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询。

 

解决方案是卸载Office 2010 32位版，重新安装Office 2010 64位版

然后在使用SQL时，需要保证数据库实例、执行查询的SQL工具、EXCEL文档、在同一服务器。

完整脚本

-- 开启导入功能
EXEC sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE

--允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Data Source=C:Users	Desktoppanziyang.xlsx')...[Sheet3$] ;

-- 关闭设置
EXEC sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',0
RECONFIGURE

-- 关闭设置 ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0