SQL 如何启用即席分布式查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14544221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to enable Ad Hoc Distributed Queries
提问by emergency coder
When I run a query with OPENROWSETin SQL Server 2000 it works.
当我OPENROWSET在 SQL Server 2000 中运行查询时，它可以工作。
But the same query in SQL Server 2008 generates the following error:
但是 SQL Server 2008 中的相同查询会生成以下错误：
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
SQL Server 阻止了对组件“Ad Hoc Distributed Queries”的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为该组件作为此服务器的安全配置的一部分被关闭。系统管理员可以使用sp_configure启用“Ad Hoc Distributed Queries”
回答by Hasib Hasan Arnab
The following command may help you..
以下命令可能对您有所帮助..
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
回答by emergency coder
You may check the following command
您可以检查以下命令
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
Or this documentation link
或者这个文档链接
回答by Robino
If ad hoc updates to system catalog is "not supported", or if you get a "Msg 5808" then you will need to configure with override like this:
如果“不支持”对系统目录的临时更新，或者如果您收到“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
回答by user2129794
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
