linux数据库开启openrowset,SQL Server 2005之通过OPENROWSET访问Excle出错

执行一下语句直接读取Excle中的数据

select *

from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:test.xls;HDR=NO','select * from [Sheet1$]')

其中HDR=NO表示不将第一行作为标题，而是作为数据读取。

在SQL Server 2005执行报以下错误：

Msg 15281, Level 16, State 1, Line 3

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

这是由于，SQL Server 2005为了安全性，安装完以后，将很多组件的默认状态设置为不启用。如果需要用到这些特性，可以由管理员手工启用。为此，SQL Server 2005还提供了一个专门的管理工具：SQL Server Surface Area Configuration

要解决本文的问题，也就是要启用AD Hoc Distributed Queries特性，执行Surface Area Configuration，选择Surface Area Configuration for features

在弹出的窗口中选择Database Engine->Ad Hoc Remote Queries，然后勾选"Enable OPENROWSET and OPENDATASOURCE support"，确认即可。