即席分布式查询服务器配置选项ad hoc distributed queries Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

默认情况下, SQL ServerSQL Server 不允许使用 OPENROWSET 和 OPENDATASOURCE 进行即席分布式查询。By default, SQL ServerSQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. 此选项设置为 1 时, SQL ServerSQL Server 允许进行即席访问。When this option is set to 1, SQL ServerSQL Server allows ad hoc access. 如果此选项未设置或设置为 0,则 SQL ServerSQL Server 不允许进行即席访问。When this option is not set or is set to 0, SQL ServerSQL Server does not allow ad hoc access.

即席分布式查询使用 OPENROWSET 和 OPENDATASOURCE 函数连接到使用 OLE DB 的远程数据源。Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET 和 OPENDATASOURCE 只应在引用不常访问的 OLE DB 数据源时使用。OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. 对于将要经常访问的数据源,应定义链接服务器。For any data sources that will be accessed more than several times, define a linked server.

重要

允许使用临时名称意味着到 SQL ServerSQL Server 的任何经过身份验证的登录名均可访问该访问接口。Enabling the use of ad hoc names means that any authenticated login to SQL ServerSQL Server can access the provider. SQL ServerSQL Server 管理员应对任何本地登录名都能安全访问的访问接口启用此功能。administrators should enable this feature for providers that are safe to be accessed by any local login.

RemarksRemarks

尝试进行即席连接但未启用“即席分布式查询”会导致错误 :消息 7415,级别 16,状态 1,行 1Attempting to make an ad hoc connection with Ad Hoc Distributed Queries not enabled results in error: Msg 7415, Level 16, State 1, Line 1

已拒绝对 OLE DB 访问接口“Microsoft.ACE.OLEDB.12.0”的即席访问。Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. 必须通过链接服务器来访问此访问接口。You must access this provider through a linked server.

示例Examples

下面的示例启用即席分布式查询,然后使用 Seattle1 函数查询名为 OPENROWSET 的服务器。The following example enables ad hoc distributed queries and then queries a server named Seattle1 using the OPENROWSET function.

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
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  

另请参阅See Also

服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
链接服务器(数据库引擎) Linked Servers (Database Engine)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
OPENDATASOURCE (Transact-SQL) OPENDATASOURCE (Transact-SQL)
sp_addlinkedserver (Transact-SQL)sp_addlinkedserver (Transact-SQL)