Excel into SQL Server with Microsoft.ACE.OLEDB.12.0

Question

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?

Answer 1

As Philip has said...first check the execution of xp_cmdshell. If it is not running due to permission issue then first reconfigure this option by running

SP_CONFIGURE 'XP_CMDSHELL',1
GO             
RECONFIGURE

after this run following command to enable linked server permissions for InProcess capabilities for ACE driver :

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

Now run this series of commands :

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

if error encountered then run each command separately. And finally run import all your excel data to SQL server by running the below mentioned command :

SELECT * INTO TargetTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0;Database=FilePath\fileName.xlsx;HDR=YES',
                         'SELECT * FROM [sheetName$]')

Remember that in case of xls you have to use Jet Driver instead of ACE. And also the TargetTableName must not be existing prior to running this query. Happy coding :)

Answer 2

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.