The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for

问题:

I'm trying to run the following statement but am receiving the error messages just below. I have researched answers to no end and none have worked for me. I'm running Office 365 (64bit). I have loaded the Microsoft Access Database Engine (64bit). This is in Visual Studio 2013 with SSDT as well as SQL Server 2012. I do not have access to changing environment or startup parameters to SQL Server. Any help is appreciated.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 
    'Excel 12.0;Database=C:\Users\UserName\Folder\SomeFile.xlsx;;HDR=NO;IMEX=1', [Table 1$])

• Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.15.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.15.0" for linked server "(null)".

Here's what I have tried:

First, I ran...

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

Followed by...with no love.

EXEC sys.sp_addsrvrolemember @loginame = N'<<Domain\User>>', @rolename = N'sysadmin';
GO

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

I have changed the code to read Microsoft.ACE.OLEDB.12.0 as I have seen that as well, still no love.

I have also checked permissions of C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local which have granted Full Control for the following: System, MSSQLSERVER, and Administrators, Network Service (on the latter).

Still no love.

Lastly, I have tried changing to the 32bit version of the Microsoft Access Database Engine which has persisted in not working.

Help, anyone?

回答1:

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue. For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.

回答2:

Instead of changing the user, I've found this advise:

https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess

This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

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

..I found an article here...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..which suggested I give Everyone full permission on this folder..

C:\Users\SQL Service account name\AppData\Local\Temp

And hey presto! My query suddenly burst into life. I punched the air in delight.

Edwaldo

回答3:

Make sure the Excel file isn't open.

回答4:

This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe "try this, then this, then this". I appreciate the other answers, but I like to pair specific solutions with specific problems

---

Error

...provider did not give information...Cannot initialize data source object...

Error Numbers

7399, 7303

Error Detail

Msg 7399, Level 16, State 1, Line 2 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 2 Cannot initialize the data source object
  of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

File was open. Close it.

Credit

• https://stackoverflow.com/a/29369868/1175496

---

Error

Access denied...Cannot get the column information...

Error Numbers

7399, 7350

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  Access denied.
Msg 7350, Level 16, State 2, Line 2 Cannot get the column information 
  from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Give access

Credit

• https://stackoverflow.com/a/27509955/1175496

---

Error

No value given for one or more required parameters....Cannot execute the query ...

Error Numbers

???, 7320

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select [Col A], [Col A] FROM　$Sheet" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". 

Solution

Column names might be wrong. Do [Col A] and [Col B] actually exist in your spreadsheet?

---

Error

"Unspecified error"...Cannot initialize data source object...

Error Numbers

???, 7303

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Run SSMS as admin. See this question.

---

Other References

Other answers which suggest modifying properties. Not sure how modifying these two properties (checking them or unchecking them) would help.

• https://stackoverflow.com/a/31605038/1175496
• http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
• https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess#3fcc14f4-420e-4544-be74-eea1e0e78462

回答5:

Here is specifically what worked for me only when the Excel file being queried was not open and when running the SQL Server Service as me [as a user that has access to the file system]. I see pieces of my answer already given elsewhere, so I apologize for any redundancy, but for the sake of a more succinct answer:

USE [master]
GO

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC 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


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

回答6:

In the SQL Server, try these steps:

1. Open one database.
2. Click in the option Server Object.
3. Click in Linked Servers.
4. Click in Providers.
5. Right click on Microsoft.ACE.OLEDB.12.0 and click Properties.
6. Uncheck all the options and close.

回答7:

I'm running SQL Server 2014 64 bit on Windows 10. I tried everything, the thing that made it work was:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

I don't know why the AllowInProcess turned off makes it work but that was the key in my case. Thank you for the suggestion of turning all the options off on the linkserver.

回答8:

With SQL 2014, I changed the SQL Server Service (MSSQL) to run as LocalSystem. This solved the problem for me.

It used to work as NT_SERVICE\MSSQL$MSSQL fine under 2008, from what I remember.

回答9:

1. Close SQL Server Management Studio. Type Services.msc in the run command to open the services window.

2. Search for SQL Server Service and right click it and select properties.

3. In the Log On Tab, select system account/or select your domain ID and Account and password.

4. Once it finds your login name press OK.

5. Now type your login’s passwords in both the fields.

6. Restart the services so that the new changes are applied as shown in figure below.

7. Now start SQL Server Management Studio and try to run the query if still not working try a system restart.

... or execute the following query:

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 

回答10:

In our case, it helped to add a parameter for SQL Server service:

1. Go to Services.msc, select SQL Server Service and open Properties.
2. Choose Startup Parameters and add new parameter –g512
3. Restart SQL server service.

回答11:

In my case this problem was occuring because i was accessing the file from shared folder by using computerName. OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='\ntpc0100\MysharedFolder\KPI_tempData\VariablePayoutDetails.xls';IMEX=1;'','.....) Insted of using computerName ntpc0100(whatever your machine name) you should specify IPaddress of your machine. for example:- OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0; Database='\193.34.23.200\MysharedFolder\KPI_tempData\KPIVariablePayoutDetails.xls'....);

回答12:

For me, these two things helped on different occasions:

1) If you've just installed the MS Access runtime, reboot the server. Bouncing the database instance isn't enough.

2) As well as making sure the Excel file isn't open, check you haven't got Windows Explorer open with the preview pane switched on - that locks it too.

回答13:

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
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

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\upload_test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT ColumnNames FROM Your_table -- Sheet Should be already Present along with headers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

回答14:

I had the exact same error message, and tried the suggested solutions in this thread but without success.

what solved the problem for me is opening the .xlsx file and saving it as .xls (excel 2003) file.

maybe the file was corrupted or in a different format, and saving it again fixed it.

回答15:

This is my error code:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'ByStore$'. Make sure the object exists and that you spell its name and the path name correctly. If 'ByStore$' is not a local object, check your network connection or contact the server administrator.".

Msg 7350, Level 16, State 2, Procedure PeopleCounter_daily, Line 26

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

My problem was the excel file was missing at the path. Just put the file with the correct sheet will do.