SQL 2008 x64读取excel 2007

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

--上面两句要先运行一下就可以了

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=Yes;IMEX=1;Database=F:/Upload/aaa.xls', 'select * from [Sheet1$]') 

当出现提示

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distr

使用以下的操作来处理

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
go
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';

RECONFIGURE;

转载于:https://www.cnblogs.com/hmII/archive/2011/07/27/2118026.html