對這文章發表回應
發表限制: 非會員 可以發表
特定分散式查詢伺服器組態選項 ad hoc distributed queries Server Configuration Option
根據預設， [SQL Server] SQL Server 不允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢。 By default, [SQL Server] SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. 當此選項設定為 1 時， [SQL Server] SQL Server 就會允許特定存取。When this option is set to 1, [SQL Server] SQL Server allows ad hoc access. 當此選項未設定或設定為 0 時， [SQL Server] SQL Server 就不允許特定存取。 When this option is not set or is set to 0, [SQL Server] SQL 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 Server] SQL Server 的任何驗證登入都可以存取該提供者。 Enabling the use of ad hoc names means that any authenticated login to [SQL Server] SQL Server can access the provider. [SQL Server] SQL Server 對於任何可安全由本機登入存取的提供者，系統管理員應該為他啟用此功能。
administrators should enable this feature for providers that are safe to be accessed by any local login.
Remarks Remarks
嘗試進行特定連接但是未啟用 特定分散式查詢 會產生錯誤：訊息 7415、層級 16、狀態 1、行 1 Attempting 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)
原文出處：ad hoc distributed queries 伺服器組態選項 - SQL Server | Microsoft Docs