We help IT Professionals succeed at work.

Microsoft Access Database Engine 2010 Redistributable and "OpenRowSet"

MarkMahon
MarkMahon asked
on
Hi -

We working with SQL Server 2008 R2 (64 bit) and installed Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe).

When I we run:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 14.0; HDR=YES; Database=\\ServerName$\Quarterly Change\QCR\InputTables\OverrideCedantDescForSorting.xls'
'SELECT * FROM [OverRideCedantDesc$]')

We get the following error messages:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

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

We have successfully run the following stored procs but still get the above error message:

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

sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
Comment
Watch Question

Author

Commented:
Okay, I started playing a bit ...

I changed "Excel 14.0" to "Excel 12.0" and path from "\\ServerName$\" to "G:\".

And it worked.

Wondering if there is a setting to make SQL Server recognize the shared network server ("ServerName") and not just the drive letter?

 

Thanks,

Mark
Actually, the path name didn't bother it either;  it was just the Excel 14.0 that was bothering it.
Could the error be in \\ServerName$ , specifically in the dollar sign in the end? It that was supposed to be administrative share, then it should be \\server\share$ ; if regular share, then \\server\share

Author

Commented:
vadimrapp1 -

your correct in the form should be what you said.  (the example I gave was inclorrect ... but my actual code has the format that you suggest).

Thanks, Mark