如何启用 Ad Hoc 分布式查询

当我在 SQLServer2000中使用 OPENROWSET运行查询时，它可以正常工作。

But the same query in SQL Server 2008 generates the following error:

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 分布式查询’ by using Sp _ configure

250094 次浏览

小开

最佳答案

下面的命令可以帮助您. 。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

小开

您可以检查以下命令

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO  --Added
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO


SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2012.HumanResources.Department
ORDER BY GroupName, Name') AS a;
GO

或者这个文件连结

小开

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

小开

如果系统目录的临时更新“不支持”，或者如果你得到一个“ Msg 5808”，那么你需要像下面这样配置:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE with override
GO