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?

Solution 1

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.

Solution 2

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 :)

Comments

[user1238918]

I found the issue. I had the file on a different SQL Server (test) and was running the query from UAT. It threw me off when the DBAs were able to run it with their accounts. Since it was a network path and it worked for them I figured it was a permission issue (which indirectly it was). The DBA was trying to tell me it was a proxy user security issue where the proxy user didn't have permission to access that folder/file (since the process will eventually be ran in SSIS) so we were both wrong and both right. Now to make sure it'll work in prod. Since you were the closest you get the cred.

[user1238918]

Ad Hoc queries are not allowed on our database servers. So that solution will not work. I have a work around where I convert the Excel file to CSV via Script Task that solves my issue. Thanks for the help though.

[Erran Morad]

I am sa on my local computer. I cannot run the stored proc you gave me. exec master..xp_cmdshell 'dir 'C:\My Temp Files\Excel\relative.xls'. I get the error Incorrect syntax near '\'. Unclosed quotation mark after the character string ''. How do correct this ?

[Our Man in Bananas]

try putting the path in double quotes like this: exec master..xp_cmdshell 'dir "C:\My Temp Files\Excel\relative.xls"'

[Scott Munro]

I also had to run SQL Server Management Studio as an administrator.

[Hybris95]

I'm not sure modifying the account running the service will be impactless. There are other ways to modify the rights of the current service account rather than changing it which can cause several issues.