本文介绍了使用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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 19:21