64bit ACE 12.0 - "Could not find installable ISAM"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

64bit ACE 12.0 - "Could not find installable ISAM"

Author

Topic

hungrytom
Starting Member

6 Posts

Posted - 2010-07-19 : 11:14:29

Hi!

I want to use openrowset to read a table or two from an excel file..

Im getting this "Could not find installable ISAM" error and after trawling the forums for days I haven't found any solutions that work for me..

Here's my query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 14.0;Database=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')

And here's the error message:

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

I am running SQL Server 2008 r2 64-bit on Windows Server 2008 64-bit. I have installed Office 2010 and AccessDatabaseEngine_X64.exe available from

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

I have tried reinstalling the ACE drivers and restarting the server. I am now slowly losing the will to live..

Please can someone help me? Any suggestions welcome..

Cheers,
Tom

deux-ex-machine
Starting Member

1 Post

Posted - 2010-07-31 : 08:32:30

Hi,

Please use this instead:

SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data\339_IMP;DBQ=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')

That should do the job
Bye

mrblackbat
Starting Member

1 Post

Posted - 2010-09-07 : 05:29:08

I encountered the same issue this morning; the OPENROWSET command seems to work if you change the version of excel to 12.0 rather than 14.0.

So in your case it would be:-

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')

sonushah81
Starting Member

1 Post

Posted - 2011-07-07 : 11:43:39

Hi
@deux-ex-machine

I tried to run using both 14 and 12 no luck.

SELECT *
INTO #test_excel
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 14.0;Database = D:\Tom_CustomSQL\Dtrak.xlsx','SELECT * FROM [Sheet1$]')

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

SELECT *
INTO #test_excel
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database = D:\Tom_CustomSQL\Dtrak.xlsx','SELECT * FROM [Sheet1$]')

Error is
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Invalid argument.".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)".

Ashah
SQL DBA
Boston

hungrytom
Starting Member

6 Posts

Posted - 2011-07-07 : 12:27:47

Hi Ashah,

The following works on our system (64-bit SQL 2008 R2, MS Office 2010):

SELECT * INTO tbl
FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data;DBQ=E:\Project Data\example.xls', 'SELECT * FROM [Sheet2$]')

HTH

Tom

I_Mar
Starting Member

1 Post

Posted - 2011-10-04 : 08:43:02

Hi guys

I have exactly the same set up as Tom

"I am running SQL Server 2008 r2 64-bit on Windows Server 2008 64-bit. I have installed Office 2010 (only Excel) and AccessDatabaseEngine_X64.exe "

I tried using

SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data\339_IMP;DBQ=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')

as it was the solution suggested but now I am getting:

"OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Procedure spImportTempFrameworksLoad, Line 18
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"."

Any help would be really appreciated!!

Thank you
Marios

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources