I can not access an excel file that has no permission issues.
I have SQL Server 2008 R2 installed on a Windows 7 32 bit PC. I down loaded and installed 'Microsoft Access database engine 2010'.
I updated the server config with:
sp_configure 'Ad Hoc Distributed Queries',1
I accessed the Microsoft.ACE.OLEDB.12.0 properties and checked 'Dynamic Parameter' and 'Allow inprocess'
I login with my windows auth account and issued the command:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0 Xml;Database=C:\Users\Public\Documents\Copy1.xlsx; HDR=YES','SELECT * FROM [Sheet1$]');
which fails with the message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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 been searching the net for more then a day now and I can not find anything that would help me resolve this issue. Any ideas?