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提供程序导入数据,这样就可以正常工作。这让我觉得这不是安全或其他环境问题。
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\work\TestData.xls;HDR=YES',
'SELECT * FROM [Sheet1$]'
)
然而,当我尝试使用应该与*.xls格式向后兼容的Microsoft.ACE.OLEDB.12.0提供程序打开*.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”但随后失败了?)
我已经尝试过多个Excel文件,并在两台不同的机器/操作系统(Windows Server 2003、Windows XP SP3)上进行了测试。两台机器都是32位的。
我还尝试重新安装AccessDatabaseEngine.exe的Office 2007和Office 2010版本(分别为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”会将错误更改为“未指定错误”。(那是一步向前吗?!)
有什么想法吗?
Data Source=' ... '
周围添加引号。 - chris neilsen