sql-server - 使用 Microsoft.ACE.OLEDB.12.0 将 Excel 导入 SQL Server

Question

尝试在 SQL Server 2008 r2 64 位中打开 Excel 文件时出现以下错误:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider 
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

我正在使用以下查询:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
HDR=NO; IMEX=1; Database=\\filepath\filename.xlsx', 'SELECT * FROM [Sheet1$]')

有趣的是，DBA 可以毫无问题地运行它。我已经完成并运行了以下查询:

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

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

运行它的帐户看起来具有 sa 访问权限。什么可能导致此问题？

Answer 1

正如菲利普所说...首先检查 xp_cmdshell 的执行情况。如果由于权限问题而未运行，则首先通过运行重新配置此选项

SP_CONFIGURE 'XP_CMDSHELL',1
GO             
RECONFIGURE

在此之后运行以下命令以启用 ACE 驱动程序的 InProcess 功能的链接服务器权限:

USE [master]
GO
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
GO

现在运行这一系列命令:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

如果遇到错误，则分别运行每个命令。最后通过运行下面提到的命令运行将所有 excel 数据导入 SQL 服务器:

SELECT * INTO TargetTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0;Database=FilePath\fileName.xlsx;HDR=YES',
                         'SELECT * FROM [sheetName$]')

请记住，在 xls 的情况下，您必须使用 Jet Driver 而不是 ACE。而且 TargetTableName 在运行此查询之前不得存在。
快乐编码:)