使用Microsoft.ACE.OLEDB.12.0将Excel转换为SQL Server

问题描述

在SQL Server 2008 r2 64位中尝试打开Excel文件时，我收到以下错误：

 消息7399，级别16，状态1，行1 
链接服务器的OLE DB提供程序Microsoft.ACE.OLEDB.12.0（null）
报告错误。提供者没有提供有关该错误的任何信息。 
消息7303，级别16，状态1，行1 
无法初始化OLE DB提供程序的数据源对象
Microsoft.ACE.OLEDB.12.0用于链接服务器（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'显示高级选项'，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 
  

运行它的帐户看起来喜欢它有访问权限。可能会导致此问题？

解决方案

您是否尝试（作为测试）将Excel文件复制到SQL Server C： \驱动器并针对该路径执行查询？

当您访问服务器并在Explorer /运行对话框中打开此路径时会发生什么： \filepath \filename.xlsx ？

您是否可以执行此查询： exec master..xp_cmdshell'dir'\filepath\filename .xlsx'？

这将帮助您确定是否是网络权利问题，还是该帐户是否具有使用分布式查询的权限。 >

我的预感是，这绝对是一个权限/权限问题，因为DBA可以运行它。

I'm getting the following error when trying to open an Excel file in SQL Server 2008 r2 64-bit:

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)".

I'm using the following query:

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

The funny thing is that the DBA can run it without issue. I've gone through and ran the following queries:

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

The account that runs it looks like it has sa access. What could be causing this issue?

解决方案

have you tried (as a test) copying the Excel file onto the SQL Server C:\ drive and executing the query against that path?

what happens when you go onto the server and open this path in Explorer/run dialog: \filepath\filename.xlsx?

Are you able to execute this query: exec master..xp_cmdshell 'dir '\filepath\filename.xlsx'?

This will help you determine if it's a network rights issue, or whether the account has the permissions to use distributed queries.

My hunch is that it's definitely a rights/permission issue, as the DBA can run it.

这篇关于使用Microsoft.ACE.OLEDB.12.0将Excel转换为SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！