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

Question

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:workTestData.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:workTestData.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:workTestData.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:workTestData.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?

Answer 1

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"";
    }