2

I'm seeing the below error message while running the following T-SQL statement.

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=123.xls',
  'SELECT * FROM [Sheet1$]')

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 have two sysadmin accounts; with one account I am able to execute the statement, however with the other account I'm seeing the error message, even with SQL account the same.

I execute the below statements, however it still doesn't work:

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

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
  • Just i tested with one SQL account by granting sysadmin rights it is working, but other sysadmins are not able to execute – Yogi Jan 8 '16 at 17:04
  • Here is a link to a similar question that had resolution. stackoverflow.com/questions/15822576/… – Mike Zalansky Jan 8 '16 at 17:24
  • @Yogi In the part of your TSQL OPENROWSET query where it is Database=123.xls' is this really Database=C:\Path\123.xls' or some other path in your environment rather than just the implicit file name? Can you confirm that the OTHER accounts that are running this query with the issue have access to READ from there? If that's the issue, please tag me back in a response and I'll be happy to add this as an answer for you to accept assuming that resolved the problem. That just seems like the obvious so I figured I'd ask and run by you. Make sure no one has the XLS file opened either. Thanks!! – Pimp Juice IT Jan 9 '16 at 0:40
  • sorry, the path is Database=C:\Path\123.xls' , i am able to acees, but other accounts ae have access issue to read the file. – Yogi Jan 12 '16 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.