How to resolve “Could not find installable ISAM.”

问题:

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?

回答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\"";
    }

回答2:

FINALLY, a solution!

Check this out: Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”

Basically, you go to

Control Panel > Administrative Tools > Component Services

then expand

Component Services > Computers > My Computer > DCOM Config

find

MSDAINITIALIZE

go to

Properties > Security > Launch and Activation Permissions

click on

Customize > Edit...

add your login name or "Everyone" if you prefer

tick ALL the "allow" boxes for the new user / group

and hit OK on both pages

Now see if your OpenRowSet / OpenDataSource command works

Thanks to Ramesh Babu Vavilla (vr.babu) from social.technet.microsoft.com for the link

回答3:

Ensuring quotations around extended properties section of the connection string fixed it for me. I had added an additional property and didn't migrate the quote to the end after my new property.

回答4:

I think the answer is hiding in the SSIS package info you posted. The new file format xlsx, stores the data in XML format instead of the old format. Look at it again. It reads... Extended Properties="Excel 12.0 XML;HDR=YES

Don't miss that XML after the standard stuff. (For what it's worth, I also read that you need "Excel 12.0 Macro" to connect with an xslm file.)

Give it a try. Weird but hopefully it works.

回答5:

Try This

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'EXCEL 12.0;DataBase=C:\TEMP\test.xlsx;Extended Properties="EXCEL 12.0 Xml;HDR=YES', [Sheet1$])

回答6:

i've resolved with this query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;HDR=NO;Database=D:\Filename.xlsx;', 
    [SheetName$])

It seems sql doesn't like the "Extended Properties" section...

回答7:

Hai am also faced this situation i solved It

Solved

string ConeectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFlp.Text 

    + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");

                OleDbConnection oconn = new OleDbConnection(ConeectionString);</b>

回答8:

This worked for me:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
   'Excel 12.0;HDR=NO;Database=C:\temp\file.xlsx;',[sheetname$])

回答9:

If you do all this post and continue with te error. try to assign permisiton on the folder pdf, to account

NT Service\MSSQLSERVER
NT Service\SQLSERVERAGENT

works for me