如何解决“找不到可安装的ISAM". OLE DB提供程序"Microsoft.ACE.OLEDB.12.0&"错误 [英] How to resolve "Could not find installable ISAM." error for OLE DB provider "Microsoft.ACE.OLEDB.12.0"

问题描述

我正在尝试使用带有"Microsoft.ACE.OLEDB.12.0" OLE DB提供程序的T-SQL OpenRowset()命令将数据从Excel 2007(.xlsx)文件导入SQL Server 2008，并且正在获取持续出现找不到可安装的ISAM"错误.所有硬件都是32位的.

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.

[12年1月10日修订，试图更加集中地关注异常情况]

以下T-SQL语句产生错误:

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$]'
)

如果我将Excel文件保存为"Excel 97-2003"格式(.xls)，并使用较旧的Microsoft.Jet.OLEDB.4.0提供程序导入数据，则可以正常工作.这使我认为这不是安全性或其他环境问题.

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$]'
)

但是，当我使用Microsoft.ACE.OLEDB.12.0提供程序尝试* .xls文件时，该文件应与* .xls格式向后兼容，它再次失败，并出现相同的错误:

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$]'
)

另外，有趣的是，当我使用SSMS导入数据..."向导时，它可以正常工作.我将导入数据"向导的输出保存为SSIS包，并查看了SSIS文件以试图弄清楚它是如何工作的，并且它已成功使用Microsoft.ACE.OLEDB.12.0提供程序.这是来自SSIS包的连接字符串:

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>

我还完成了相关的SQL Server配置，以允许OPENROWSET分布式查询:

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

如果我还设置了以下* sp_MSset_oledb_prop *值(我在某处的帖子中找到了这些值)...

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

但是，我不确定这是上游还是下游错误. (现在是否找到了可安装的ISAM"，但随后失败了?)

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

我已经在两个不同的机器/操作系统(Windows Server 2003，Windows XP SP3)上使用多个Excel文件尝试了此操作.两台机器都是32位的.

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

我还尝试过重新安装Access DatabaseEngine.exe的Office 2007和Office 2010版本( http://www.microsoft.com/download/zh-cn/details.aspx?id=13255 ).

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.

总结:

• "Microsoft.Jet.OLEDB.4.0"提供程序可以使用T-SQL，但"Microsoft.ACE.OLEDB.12.0"则不能.
• "Microsoft.ACE.OLEDB.12.0"使用导入数据..."向导工作(据我从保存的SSIS作业文件中得知).
• 将"AllowInProcess"和"DynamicParameters"属性设置为"1"会将错误更改为未指定错误". (这是前进的一步吗?！)

有什么想法吗?

推荐答案

请尝试一下，它可能会对您有所帮助:

TRY this it may help you:

根据要求设置path和strFileType

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

这篇关于如何解决“找不到可安装的ISAM". OLE DB提供程序"Microsoft.ACE.OLEDB.12.0&"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！