一、安装:AccessDatabaseEngine_X64 64位sql查询office1
二、开启
--启用Ad Hoc Distributed Queries:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
三、查询
select *
from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 8.0;HDR=Yes;IMEX=1;Database=E:/exl/CunHuo.xls',[Sheet1$]) as a
where 1=1
四、如果提示还是不通(高版本sql 2016)
--允许在进程中使用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