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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

Make sure the Excel file isn't open.

Answer 4

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.

Answer 5

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 

Answer 6

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