SQL Server and Microsoft.ACE.OLEDB.12.0

Question

I am getting the following:

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

When I execute the following from a Stored Procedure in SQLServer -

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\Users\v-bimoss\Source\Workspaces\External\Onesite\URLoadDB\Templates\T1-Current.xlsx', 'SELECT * FROM [Sheet1$]')

I have been through all the threads I could find on this topic - but still am having the problem

I have verified that:

1. The 64 bit Microsoft.ACE.OLEDB.12.0 drivers are loaded

2. Ad Hoc Distributed Queries is set to 1

3. Dynamic Parameter and Allow Inprocess are both set to 1

4. Changed MSSQL to run under my account

5. The account that is running MSSQL and the account I am using both have full access to C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Any help would be appreciated. -wmm

Answer 1

I had the same problem. After going through a number of answers installing the provider, dealing with the cryptic errors, I had a brief moment of joy when I didn't get an immediate error- instead the query never returned...

I finally got it working after wasting more than a day on it. The final fix was to change SQL Server to the Local System account to login.

Here's what I did:

1. Ensure Office and SQL Server have the same bit width (64-bit in my case).

2. Install ACE provider for 64-bit (Jet for 32-bit)

3. Configure server

   USE [MSDB]
   GO
   
   sp_configure 'show advanced options', 1
   GO
   RECONFIGURE WITH OverRide
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1
   GO
   RECONFIGURE WITH OverRide
   GO
   
   USE [master]
   GO
   
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 
   GO 
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 
   GO 
   

4. Configure SQL logon. In SQL Server Configuration Manager

   • Open the server Properties -> Log On -> Log on as:
   • Change to Built-in account - Local System

5. Finally, here's my query:

   SELECT * FROM OPENROWSET(
   'Microsoft.ACE.OLEDB.16.0'
   ,'Excel 12.0;Database=C:\Temp\Test.xlsx;HDR=YES'
   ,'SELECT * FROM [Sheet1$]')
   

Answer 2

Sadly, OPENROWSET is know to be buggy for ACE. So there are a bunch of different things to try when trying to tweak it. Adding and removing a few of the following suggestions will hopefully do it for you:

1) xlsx files need to be called with Excel 12.0 Xml;

2) HDR=YES; Tells if a header record is present. It'll crash if there is and you have numerics. Change to NO if there is no header.

3) IMEX=1; This allows for mixed numeric and text fields. In case people start mixing letters in with your numerics.

4) There can be registry problems based on ACE 12.0 vs ACE 14.0. Double check your version.

5) Lastly, weirder than weird... OPENROWSET crashes when you have comments (or don't have comments). If you have --comment, by putting a space after the dashes, -- comment it sometimes helps. So if you have comments, try removing them or rearranging them and you may get a surprise. (I know... it's really a sad state of affairs.)

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=c:\Users\v-bimoss\Source\Workspaces\External\Onesite\URLoadDB\Templates\T1-Current.xlsx', 'SELECT * FROM [Sheet1$]')

Anyway, since OPENROWSET can be a bit unstable, you may want to import data using a different approach with staging tables, or csv files, etc. But if you get it working, it can be quite nice.

Hope that helps :)