[修订1/10/12试图更加突出地关注异常]
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 97-2003”格式(.xls)保存Excel文件,并使用较旧的Microsoft.Jet.OLEDB.4.0提供程序导入数据,它的工作原理很好.这让我觉得这不是安全或其他环境问题.
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格式,它再次失败并出现相同的错误:
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包中的连接字符串:
<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分布式查询:
sp_configure 'show advanced options',1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries',1 reconfigure GO
如果我还设置了以下* sp_MSset_oledb_prop *值(我在某个帖子中发现)…
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
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”,然后发生故障?)
我已经在两个不同的机器/操作系统(Windows Server 2003,Windows XP SP3)上尝试使用多个Excel文件.两台机器都是32位.
我还尝试重新安装Office 2007和Office 2010版本的AccessDatabaseEngine.exe(分别为http://www.microsoft.com/download/en/details.aspx?id=23734和http://www.microsoft.com/download/en/details.aspx?id=13255),无济于事.
总结:
>“Microsoft.Jet.OLEDB.4.0”提供者使用T-sql,但是“Microsoft.ACE.OLEDB.12.0”没有.
>“Microsoft.ACE.OLEDB.12.0”使用“导入数据…”向导工作(据我所知,从保存的SSIS作业文件).
>将“AllowInProcess”和“DynamicParameters”属性设置为“1”将错误更改为“未指定错误”. (这是向前迈进吗?!)
有什么想法吗?
解决方法
根据需求设置路径和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\""; }