SQL Server:无法初始化OLE DB提供程序"Microsoft.ACE.OLEDB.12.0&"的数据源对象.用于链接服务器“(null)" [英] SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

问题描述

我正在尝试运行以下查询:

I am trying to run the following query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Somefile.xlsx',
'SELECT * FROM [Sheet$]')

但是我得到这个错误:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

我尝试了以下操作:

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

并且:

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

当我转到服务器对象"->链接服务器"->提供程序"时，将列出Microsoft.ACE.OLEDB.12.0.

When I go to 'Server Objects' -> 'Linked Servers' -> 'Providers', Microsoft.ACE.OLEDB.12.0 is listed.

已安装Acess Database Engine x64，所有Office产品都是64位，而我的SQL Server也是64位.

Acess Database Engine x64 is installed, all Office products are 64-bit, and my SQL Server is also 64 bit.

如果有关系，所有用户都可以访问用户"文件夹中的临时"文件夹.

If it matters, all users have access to the Temp folder in the Users folder.

(这些都是我在对该问题的类似答案中找到的所有建议)

(These are all suggestions I found in similar answers to this question)

使用SQL Server 2014.

Using SQL Server 2014.

推荐答案

请确保您关闭excel电子表格并以管理员身份运行SSMS.

Make sure you close the excel spreadsheet and run SSMS as admin.

这篇关于SQL Server:无法初始化OLE DB提供程序"Microsoft.ACE.OLEDB.12.0&"的数据源对象.用于链接服务器“(null)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！