特定分散式查詢伺服器組態選項ad hoc distributed queries Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否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)
連結的伺服器 (Database Engine) Linked Servers (Database Engine)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
OPENDATASOURCE (Transact-SQL) OPENDATASOURCE (Transact-SQL)
sp_addlinkedserver (Transact-SQL)sp_addlinkedserver (Transact-SQL)