Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to resolve "Could not find installable ISAM." error for OLE DB provider "Microsoft.ACE.OLEDB.12.0"

I am trying to import data from Excel 2007 (.xlsx) files into SQL Server 2008 using a T-SQL OpenRowset() command with the "Microsoft.ACE.OLEDB.12.0" OLE DB provider, and I'm getting a persistent "Could not find installable ISAM" error. All hardware is 32-bit.

[Revised 1/10/12 to try to focus more sharply on the anomalies]

The following T-SQL statement produces the error:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\work\TestData.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES"',
    'SELECT * FROM [Sheet1$]'
)

If I save the Excel file in the "Excel 97-2003" format (.xls) and use the older Microsoft.Jet.OLEDB.4.0 provider to import the data, it works just fine. This makes me think it is not a security or other environmental issue.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\work\TestData.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]'
)

However, when I try the *.xls file with Microsoft.ACE.OLEDB.12.0 provider, which should be backward compatible with the *.xls format, it again fails with the same error:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\work\TestData.xls;Extended Properties="Excel 8.0;HDR=YES";', 
    'SELECT * FROM [Sheet1$]'
)

Also, interestingly, when I use the SSMS "Import Data..." wizard, it works fine. I saved the Import Data wizard output as an SSIS package and looked in the SSIS file to try to figure out how it works, and it IS successfully using the Microsoft.ACE.OLEDB.12.0 provider. This is the connection string from the SSIS package:

<DTS:Property DTS:Name="ConnectionString">
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\TestData.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
</DTS:Property>

I've also done the relevant SQL Server configuration to allow the OPENROWSET distributed query:

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO

If I also set the following *sp_MSset_oledb_prop* values (which I found in a post somewhere)...

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 

...then the error changes to "Unspecified error":

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

However, I am not sure if this an upstream or downstream error. (Is it now finding the "installable ISAM" but failing subsequently?)

I have tried this with multiple Excel files on two different machines/OSes (Windows Server 2003, Windows XP SP3). Both machines are 32-bit.

I've also tried re-installing both the Office 2007 and Office 2010 versions of AccessDatabaseEngine.exe (http://www.microsoft.com/download/en/details.aspx?id=23734 and http://www.microsoft.com/download/en/details.aspx?id=13255, respectively), to no avail.

To summarize:

  • "Microsoft.Jet.OLEDB.4.0" provider works using T-SQL, but "Microsoft.ACE.OLEDB.12.0" does not.
  • "Microsoft.ACE.OLEDB.12.0" works using the "Import Data..." wizard (as far as I can tell from the saved SSIS job file).
  • Setting the "AllowInProcess" and "DynamicParameters" properties to "1" changes the error to "Unspecified error". (Is that a step forward?!)

Any thoughts?

like image 659
Martin_W Avatar asked Jan 10 '12 02:01

Martin_W


People also ask

Could not connect to database could not find installable ISAM?

In the Workbook Connections dialog box, select the connection to the Access database, and then click Properties. In the Connection Properties dialog box, click the Definition tab. In the Connection Properties dialog box, click OK. At this point your data should be refreshed.

Could not connect to database could not find installable ISAM Automation Anywhere?

Patel (Automation Anywhere Inc.) Cause: When an Excel workbook that's linked to an Access database is opened in a later version of Excel, parameters may be added to the connection string. If the earlier version of Excel does not recognize these parameters, it triggers the error message.


1 Answers

TRY this it may help you:

set path and strFileType as per requirement

      string connString = "";
//    string strFileType = Path.GetExtension(UpfileName.FileName).ToLower();
//    string path = UpfileName.PostedFile.FileName;

if (strFileType.Trim() == ".xls")
   {

      connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
   }
   else if(strFileType.Trim() == ".xlsx")
    {
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
    }
like image 152
Sagar R Avatar answered Oct 16 '22 09:10

Sagar R