SQL Server 2008 x64。
SQL Server 2008 R2 x64。
「分散式查詢(Distributed Queries)」可存取多個異質資料來源的資料。
這些資料來源可以儲存在相同或不同的電腦上。
Microsoft SQL Server 藉由使用 OLE DB 來支援分散式查詢。
當 OLE DB 資料來源不會被經常參考，因而不保證可設定連結伺服器時，您就可以使用特定名稱來做為資料表參考。
在 SQL Server 中，您可以使用 OPENROWSET 與 OPENDATASOURCE 函數來提供特定名稱。
根據預設，SQL Server 不允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢。
也就是說，「特定分散式查詢(Ad Hoc Distributed Queries)」選項是停用的。
當此選項設定為 1 時，SQL Server 就會允許特定存取。當此選項未設定或設定為 0 時，SQL Server 就不允許特定存取。
特定分散式查詢會使用 OPENROWSET 和 OPENDATASOURCE 函數，連接到使用 OLE DB 的遠端資料來源。
OPENROWSET 與 OPENDATASOURCE 只能用來參考不常存取的 OLE DB 資料來源。
對於經常存取的資料來源，請定義連結伺服器。
如果啟用特定名稱的使用，就代表 SQL Server 的任何驗證登入都可以存取該提供者。
SQL Server 管理員應該針對由任何本機登入存取都很安全的提供者啟用此功能。
如需詳細資訊，請參閱＜存取外部資料＞中的 DisallowAdhocAccess 選項。
尚未啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項之錯誤訊息：
--01 尚未啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項之錯誤訊息
錯誤訊息： 訊息 15281，層級 16，狀態 1，行 1 SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。 系統管理員可以使用 sp_configure 來啟用 'Ad Hoc Distributed Queries' 的使用。 如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。 Msg 15281, Level 16, State 1, Line 1 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. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
若要啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項，請參考以下的文章：
SQL Server 2008 介面區組態(Surface Area Configuration，SAC)：
啟用 Ad Hoc Distributed Queries。訊息 15281，SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。
http://sharedderrick.blogspot.com/2011/02/sql-server-2008-surface-area.html
在啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項後，就可以使用 OPENROWSET 與 OPENDATASOURCE 函數。
以下為使用 OPENROWSET 與 OPENDATASOURCE 函數的範例程式碼：
(一)使用 OPENROWSET 函數
包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。
這個方法是存取連結伺服器資料表的另一個方法，而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。
對於更常用到的 OLE DB 資料來源參考，請改用連結的伺服器。
您可以依照資料表名稱的相同方式，在查詢的 FROM 子句中參考 OPENROWSET 函數。
根據 OLE DB 提供者的能力而定，OPENROWSET 函數也可以被當做 INSERT、UPDATE 或 DELETE 陳述式的目標資料表加以參考。
雖然查詢可以傳回多個結果集，但是 OPENROWSET 只能傳回第一個。
OPENROWSET 也支援透過內建 BULK 提供者執行大量作業，可讓檔案資料被讀取，並且當做資料列集傳回。
OPENROWSET 權限是由傳遞給 OLE DB 提供者之使用者名稱的權限所決定。
若要使用 BULK 選項，需要 ADMINISTER BULK OPERATIONS 權限。
--EX1. SQL Server Native Client OLE DB 提供者來使用 OPENROWSET
/*
下列範例會利用 SQL Server Native Client OLE DB 提供者來存取遠端伺服器 localhost\I2 上 Northwind_TW 資料庫中的 客戶 資料表。
(使用 SQLNCLI 和 SQL Server 將會重新導向至最新版的 SQL Server Native Client OLE DB 提供者)。
SELECT 陳述式是用來定義傳回的資料列集。提供者字串包含 Server 和 Trusted_Connection 關鍵字。
這些關鍵字是由 SQL Server Native Client OLE DB 提供者所辨識。
*/
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=localhost\I2 ;Trusted_Connection=yes;',
'SELECT 客戶編號,公司名稱,地址 FROM Northwind_TW.dbo.客戶') AS a;
GO
--EX2. 使用 Microsoft OLE DB Provider for Jet
/*
注意：
1. Microsoft.Jet.OLEDB.4.0 目前沒有 x64 位元版本。
2. 在 x64 位元作業系統 + SQL Server 2008 x64 位元，可以安裝 x64 位元版本的 「Microsoft Access Database Engine 2010 可轉散發套件」。
*/
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\myAdmin\OtherSource\北風貿易01.mdb';'admin';'',客戶);
GO
/* 錯誤訊息
訊息 7308，層級 16，狀態 1，行 1
OLE DB 提供者 'Microsoft.Jet.OLEDB.4.0' 不能用來散佈查詢，因為提供者是設定成以單一執行緒 Apartment 模式執行。
訊息 7308，層級 16，狀態 1，行 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
*/
--02 在 SQL Server 2008 R2 x64 版本，但 Microsoft.Jet.OLEDB.4.0 目前沒有 x64 位元版本，產生的錯誤：
--EX3. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.accdb
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\myAdmin\OtherSource\NW2010tw.accdb';'admin';'',客戶);
GO
/*
沒有安裝 x64 位元版本時的錯誤：
訊息 7302，層級 16，狀態 1，行 1
無法建立連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體。
*/
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\myAdmin\OtherSource\NW2010tw.accdb';'admin';'','SELECT * FROM [供應商]');
GO
--EX4. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.mdb
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\myAdmin\OtherSource\NW2ktw.mdb';'admin';'',客戶);
GO
--
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\myAdmin\OtherSource\NW2ktw.mdb';'admin';'','SELECT * FROM [供應商]');
GO
--EX5. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.xls
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\myAdmin\OtherSource\NW2ktw.xls;HDR=YES;IMEX=1','SELECT * FROM [客戶$]');
GO
--EX6. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.xlsx
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\myAdmin\OtherSource\NW2010tw.xlsx;HDR=YES;IMEX=1','SELECT * FROM [客戶$]');
GO
(二)使用 OPENDATASOURCE 函數
語法：OPENDATASOURCE ( provider_name, init_string )
提供特定連接資訊做為四部分物件名稱，而不使用連結伺服器名稱。
唯有針對指定的提供者將 DisallowAdhocAccess 登錄選項明確設為 0 時，且已啟用 [特定分散式查詢] 進階組態選項時，才可使用 OPENDATASOURCE 來存取 OLE DB 資料來源的遠端資料。
若未設定這些選項，預設行為便不允許特定存取。
OPENDATASOURCE 函數可使用於與連結伺服器名稱相同的 Transact-SQL 語法位置。
因此，OPENDATASOURCE 可做為四部分名稱的第一部分使用，來參考 SELECT、INSERT、UPDATE 或 DELETE 陳述式中的資料表或檢視名稱，或參考 EXECUTE 陳述式中的遠端預存程序。
執行遠端預存程序時，OPENDATASOURCE 應該參考 SQL Server 的另一個執行個體。
OPENDATASOURCE 不接受變數做為其引數。
如同 OPENROWSET 函數，OPENDATASOURCE 只應該參考不常存取的 OLE DB 資料來源。請為存取多次的資料來源定義連結伺服器。
OPENDATASOURCE 或 OPENROWSET 都不提供連結伺服器定義的所有功能，例如安全性管理和查詢目錄資訊的能力。
每次在呼叫 OPENDATASOURCE 時，都必須提供所有連接資訊，包括密碼在內。
重要事項：
Windows 驗證比 SQL Server 驗證更安全。
可能的話，您應該使用 Windows 驗證。OPENDATASOURCE 不應與連接字串中的明確密碼一起使用。
--EX1. SQL Server Native Client OLE DB 提供者來使用 OPENDATASOURCE
SELECT *
FROM OPENDATASOURCE('SQLNCLI','Data Source=Localhost\I2;Integrated Security=SSPI').Northwind_TW.dbo.客戶;
GO
--EX2. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.mdb
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\myAdmin\OtherSource\NW2ktw.mdb";User ID=Admin;Password=;')...[客戶];
GO
--EX3. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.accdb
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\myAdmin\OtherSource\NW2010tw.accdb";User ID=Admin;Password=;')...[客戶];
GO
--EX4. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.xls
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\myAdmin\OtherSource\NW2ktw.xls";Extended Properties=EXCEL 12.0')...[客戶$];
GO
--EX5. 使用 Microsoft.ACE.OLEDB.12.0，連接到 *.xlsx
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\myAdmin\OtherSource\NW2010tw.xlsx";Extended Properties=EXCEL 12.0')...[客戶$];
GO
認識 OPENROWSET 與 OPENDATASOURCE 函數
當 OLE DB 資料來源不會被經常參考，因而不保證可設定連結伺服器時，您就可以使用特定名稱來做為資料表參考。
在 SQL Server 中，您可以使用 OPENROWSET 與 OPENDATASOURCE 函數來提供特定名稱。
若要控制可存取特定資料來源的人員，只能透過連結伺服器來進行。
針對 SQL Server 的每個執行個體，系統管理員 (sysadmin) 固定伺服器角色的成員可以使用 DisallowAdhocAccess 提供者選項及 Ad Hoc Distributed Queries 伺服器組態選項，
來啟用或停用 OLE DB 提供者的特定連接子名稱。
當啟用特定存取時，登入該執行個體的任何使用者都可以執行包含特定連接子的名稱的 SQL 陳述式，連接子名稱會參照可使用 OLE DB 提供者來存取之網路上的任何資料來源。
若要控制資料來源的存取權，系統管理員 (sysadmin) 角色的成員可以停用 OLE DB 提供者的特定存取權，進而限制使用者只能存取由系統管理員定義之連結伺服器名稱所參考的資料來源。
依預設，會啟用 SQL Server Native Client OLE DB Provider 的特定存取權，並停用所有其他 OLE DB 提供者的特定存取權。
OPENROWSET 及 OPENDATASOURCE 都有提供特定連接資訊。
您可以使用這二個函數來指定存取 OLE DB 資料來源所需的所有資訊；然而，您不能交替使用 OPENROWSET 及 OPENDATASOURCE：
(1)
每當 OLE DB 提供者藉由指定資料表 (或檢視) 名稱，或藉由指定會傳回資料列集的查詢來傳回資料列集時，您就可使用 OPENROWSET 函數。
OPENROWSET 可用來代替 Transact-SQL 陳述式中的資料表或檢視名稱。
(2)
唯有當提供者公開資料列集，並使用 catalog.schema.object 標記法時，才能使用 OPENDATASOURCE。
在 Transact-SQL 語法中，可以使用連結伺服器名稱的地方，就可以使用 OPENDATASOURCE。
因此，在 catalog.schema.object 標記法中，OPENDATASOURCE 可做為參考資料表或檢視之四部份名稱的第一部份。
應該只有在無法設定永久性連結伺服器的特定情況下，才使用 OPENROWSET 和 OPENDATASOURCE 來存取外部資料。
OPENROWSET 和 OPENDATASOURCE 並沒有提供連結伺服器的所有功能，例如：管理登入對應、查詢連結伺服器之中繼資料的功能，以及設定各種連接設定 (例如逾時值) 的功能。
SQL Server 在連接到另一個資料來源時，會模擬適用於 Windows 驗證登入的登入方式；然而，SQL Server 無法模擬 SQL Server 的驗證登入。
因此，若為 SQL Server 驗證登入，SQL Server 可以使用執行 SQL Server 服務之 Windows 帳戶的安全性內容，來存取其他資料來源，例如：檔案、非關聯式資料來源 (如 Active Directory)。
這麼做可能會讓這些登入存取他們沒有權限的其他資料來源 (因為執行 SQL Server 服務的帳戶擁有該權限)。
當您使用 SQL Server 驗證登入時，應考量這種可能性。
OPENROWSET 和 OPENDATASOURCE 的引數不支援變數。
這些引數必須指定成字串常值。如果變數必須當成引數來傳遞，則可用動態方式來建構包含該變數的查詢字串，並使用 EXECUTE 陳述式來執行。
綜合前述來看：
(1)
使用 OPENROWSET 函數，其語法架構是：
SELECT * FROM OPENROWSET ('連線字串','SQL語法')
(2)
使用 OPENDATASOURCE 函數，其語法架構是：
SELECT * FROM OPENDATASOURCE ('連線字串').Database.Owner.Object
或是
SELECT * FROM OPENDATASOURCE ('連線字串').Database.Schame.Object
也就是使用「多部分名稱」、「四部分的名稱」：server_name .[database_name].[schema_name].object_name。
在 x64 位元作業系統 + SQL Server 2008 x64 位元，可以安裝 x64 位元版本的 「Microsoft Access Database Engine 2010 可轉散發套件」。
請到以下的網址下載：
Microsoft Access Database Engine 2010 可轉散發套件
http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displayLang=zh-tw
--03 下載Access Database Engine 2010 可轉散發套件
參考資料
遭遇錯誤： Msg 7308、Msg 7320、Msg 7415(訊息 7302、訊息 7308、訊息 7415)；以使用 OPENROWSET 與 OPENDATASOURCE 函數為例
http://sharedderrick.blogspot.com/2011/08/msg-7308msg-7320msg-7415-7302-7308-7415.html
分散式查詢
http://msdn.microsoft.com/zh-tw/library/ms188721.aspx
OPENROWSET (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190312.aspx
OPENDATASOURCE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms179856.aspx
使用特定名稱來識別資料來源
http://msdn.microsoft.com/zh-tw/library/ms187873.aspx
設定伺服器組態選項
http://msdn.microsoft.com/zh-tw/library/ms189631.aspx
特定分散式查詢選項
http://msdn.microsoft.com/zh-tw/library/ms187569.aspx
設定分散式查詢的 OLE DB 提供者
http://msdn.microsoft.com/zh-tw/library/ms190918.aspx
Connecting via a linked server to an access 2010 database file
https://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file
Cannot create instance of "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" on x64 Vista with x64 SQL Server 2008
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/4887d91f-6ac7-40c0-9fc8-5cdd0634e603
Microsoft Access Database Engine 2010 可轉散發套件
http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displayLang=zh-tw
啟用 Ad Hoc Distributed Queries。訊息 15281，SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。
http://sharedderrick.blogspot.com/2011/02/sql-server-2008-surface-area.html
沒有留言:
張貼留言