摘要
Microsoft SQL Server 支援以持續或臨機操作的方式連線到其他 OLE DB 資料來源。 持續連線稱為連結伺服器；為了單一查詢而進行的臨機操作連線稱為分散式查詢。
Microsoft Excel 活頁簿就是一種 OLE DB 資料來源，可讓您以這種方式透過 SQL Server 進行查詢。 本文將告訴您，將 Excel 資料來源設定為連結伺服器所需的語法，以及使用分散式查詢來查詢 Excel 資料來源所需的語法。
其他相關資訊
在連結伺服器上查詢 Excel 資料來源
您可以使用 SQL Server Management Studio 或 Enterprise Manager (系統預存程序)、SQL-DMO (分散式管理物件) 或 SMO (SQL Server Management Objects) 將 Excel 資料來源設定為 SQL Server 連結伺服器 (SMO 只適用於 Microsoft SQL Server 2005)。 不論何種狀況，您都必須設定下列四個屬性：
-
您要連結的伺服器使用的名稱。
-
連線所要使用的 OLE DB 提供者。
-
資料來源或 Excel 活頁簿的完整路徑和檔案名稱。
-
可將目標識別為 Excel 活頁簿的提供者字串。 根據預設，Jet 提供者預期的是 Access 資料庫。
系統預存程序 sp_addlinkedserver 還需要 @srvproduct 屬性，這可以是任何字串值。
注意：如果您使用 SQL Server 2005，則必須為 SQL Server Management Studio 中的 [產品名稱]屬性，或為 Excel 資料來源的預存程序中的 @srvproduct 屬性指定一個不是空白的值。
使用 SQL Server Management Studio 或 Enterprise Manager 將 Excel 資料來源設定為連結伺服器
SQL Server Management Studio (SQL Server 2005)
-
在 SQL Server Management Studio 中，展開 [物件總管] 中的 [伺服器物件]。
-
以滑鼠右鍵按一下 [連結的伺服器]，然後按一下 [新增連結的伺服器]。
-
在左窗格中，選取 [一般] 頁面，然後依照下列步驟執行：
-
在第一個文字方塊中，為連結伺服器輸入任何名稱。
-
選取 [其他資料來源] 選項。
-
在 [提供者] 清單中，按一下 [Microsoft Jet 4.0 OLE DB Provider]。
-
在 [產品名稱] 方塊中，輸入 Excel 作為 OLE DB 資料來源的名稱。
-
在 [資料來源] 方塊中，輸入 Excel 檔案的完整路徑和檔案名稱。
-
在 [提供者字串] 方塊中，輸入 Excel 8.0 代表 Excel 2002、Excel 2000 或 Excel 97 活頁簿。
-
按一下 [確定] 建立新的連結伺服器。
-
附註：在 SQL Server Management Studio 中，無法展開新的連結伺服器名稱來檢視伺服器所含物件的清單。
Enterprise Manager (SQL Server 2000)
-
在 Enterprise Manager 中，按一下以展開 [安全性] 資料夾。
-
以滑鼠右鍵按一下 [連結的伺服器]，然後按一下 [新增連結的伺服器]。
-
在 [一般] 索引標籤上，依照下列步驟執行：
-
在第一個文字方塊中，為連結伺服器輸入任何名稱。
-
在 [伺服器類型] 方塊中，按一下 [其他資料來源]。
-
在 [提供者名稱] 清單中，按一下 [Microsoft Jet 4.0 OLE DB Provider]。
-
在 [資料來源] 方塊中，輸入 Excel 檔案的完整路徑和檔案名稱。
-
在 [提供者字串] 方塊中，輸入 Excel 8.0 代表 Excel 2002、Excel 2000 或 Excel 97 活頁簿。
-
按一下 [確定] 建立新的連結伺服器。
-
-
按一下以展開新的連結伺服器名稱，來展開它所包含的物件清單。
-
在新的連結伺服器名稱下方，按一下 [資料表]。 請注意，您的工作表和已命名的範圍會出現在右窗格中。
使用預存程序將 Excel 資料來源設定為連結伺服器
您也可以使用系統預存程序 sp_addlinkedserver 將 Excel 資料來源設定為連結的伺服器：
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
如上所述，這個預存程序另外還需要 @srvproduct 引數的任意字串值，它在 Enterprise Manager 和 SQL Server Management Studio 設定中顯示為「產品名稱」。 @location 和 @catalog 引數則用不到。
使用 SQL-DMO 將 Excel 資料來源設定為連結伺服器
您可以使用「SQL 分散式管理物件」從 Microsoft Visual Basic 或其他程式語言中，以程式設計的方式將 Excel 資料來源設定為連結伺服器。 您必須提供和 Enterprise Manager 以及 SQL Server Management Studio 設定所需相同的四個引數。
Private Sub Command1_Click()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "(local)", "sa", "password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "XLTEST_DMO"
.ProviderName = "Microsoft.Jet.OLEDB.4.0"
.DataSource = "c:\book1.xls"
.ProviderString = "Excel 8.0"
End With
s.LinkedServers.Add ls
s.Close
End Sub
使用 SMO 將 Excel 資料來源設定為連結伺服器
在 SQL Server 2005 中，您可以使用 SQL Server Management Objects (SMO) 以程式方式將 Excel 資料來源設定為連結伺服器。 如果要執行這項操作，可以使用 Microsoft Visual Basic .NET 或其他程式語言。 您必須提供 SQL Server Management Studio 設定中需要的引數。 SMO 物件模型會延伸及取代 Distributed Management Objects (SQL-DMO) 物件模型。 由於 SMO 與 SQL Server 7.0 版、SQL Server 2000 和 SQL Server 2005 相容，因此您也可以使用 SMO 來設定 SQL Server 2000。
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim s As Server
Dim conn As ServerConnection
Dim ls As LinkedServer
conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
s = New Server(conn)
Try
ls = New LinkedServer(s, "XLTEST_DMO")
With ls
.ProviderName = "Microsoft.Jet.OLEDB.4.0"
.ProductName = "Excel"
.DataSource = "c:\book1.xls"
.ProviderString = "Excel 8.0"
End With
ls.Create()
MessageBox.Show("New linked Server has been created.")
Catch ex As SmoException
MessageBox.Show(ex.Message)
Finally
ls = Nothing
If s.ConnectionContext.IsOpen = True Then
s.ConnectionContext.Disconnect()
End If
End Try
End Sub
End Class
在連結伺服器上查詢 Excel 資料來源
在將 Excel 資料來源設定為連結伺服器之後，就可以從 Query Analyzer 或其他用戶端應用程式輕鬆查詢資料。 例如，如果要擷取 Excel 檔案中儲存在 Sheet1 的資料列，下列程式碼會使用您用 SQL-DMO 設定的連結伺服器：
SELECT * FROM XLTEST_DMO...Sheet1$
您也可以使用 OPENQUERY 以 Passthrough 的方式查詢 Excel 連結伺服器，如下所示：
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
OPENQUERY 需要的第一個引數是連結伺服器名稱。 如上所示，工作表名稱必須使用分隔符號。
您也可以使用下列查詢，取得 Excel 連結伺服器上可用的所有資料表清單：
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
使用分散式查詢來查詢 Excel 資料來源
您可以使用 SQL Server 分散式查詢和 OPENDATASOURCE 或 OPENROWSET 函數，以臨機操作方式查詢不常存取的 Excel 資料來源。
注意：如果您使用 SQL Server 2005，請確定已使用 SQL Server 介面區組態來啟用 [隨選分散式查詢] 選項，如下例中所示：
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
請注意，OPENROWSET 使用少見的語法做為第二個 (提供者字串) 引數：
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\book1.xls', Sheet1$)
ActiveX Data Objects (ADO) 開發者使用 OPENROWSET 時，可能會以下列語法使用第二個 (提供者字串) 引數：
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
此語法會導致 Jet 提供者回應下列錯誤：
找不到可安裝的 ISAM。
注意：如果您輸入 DataSource 而非 Data Source，也會發生這個錯誤。 例如，下列引數是不正確的：
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
參考資料
因為 SQL Server 連結伺服器和分散式查詢使用 OLE DB Provider，所以此處適用有關搭配 ADO 使用 Excel 時的一般性指導方針和注意事項。
如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
257819 如何從 Visual Basic 或 VBA 搭配使用 ADO 與 Excel 資料 (英文)
如需有關 SQL Server Management Objects 的詳細資訊，請造訪下列 Microsoft Developer Network (MSDN) 網站：
http://msdn2.microsoft.com/zh-tw/library/ms162169(ide).aspx如需有關如何啟用 [隨選分散式查詢] 選項的詳細資訊，請造訪下列 MSDN 網站：
