您目前已離線,請等候您的網際網路重新連線

如何搭配使用 Excel 以及 SQL Server 連結伺服器和分散式查詢

結論
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)
  1. 在 SQL Server Management Studio 中,展開 [物件總管] 中的 [伺服器物件]
  2. 用滑鼠右鍵按一下 [連結伺服器],然後按一下 [新增連結伺服器]
  3. 在左窗格中,選取 [一般] 頁面,然後依照下列步驟執行:
    1. 在第一個文字方塊中,為連結伺服器輸入任何名稱。
    2. 選取 [其他資料來源] 選項。
    3. [提供者] 清單中,按一下 [Microsoft Jet 4.0 OLE DB Provider]
    4. [產品名稱] 方塊中,輸入 Excel 作為 OLE DB 資料來源的名稱。
    5. [資料來源] 方塊中,輸入 Excel 檔案的完整路徑和檔案名稱。
    6. [提供者字串] 方塊中,輸入 Excel 8.0 代表 Excel 2002、Excel 2000 或 Excel 97 活頁簿。
    7. 按一下 [確定] 以建立新的連結伺服器。
注意 在 SQL Server Management Studio 中,無法展開新的連結伺服器名稱來檢視伺服器內含物件的清單。
Enterprise Manager (SQL Server 2000)
  1. 在 Enterprise Manager 中,按一下以展開 [安全性] 資料夾。
  2. 用滑鼠右鍵按一下 [連結伺服器],然後按一下 [新增連結伺服器]
  3. [一般] 索引標籤上,依照下列步驟執行:
    1. 在第一個文字方塊中,為連結伺服器輸入任何名稱。
    2. [伺服器類型] 方塊中,按一下 [其他資料來源]
    3. [提供者名稱] 清單中,按一下 [Microsoft Jet 4.0 OLE DB Provider]
    4. [資料來源] 方塊中,輸入 Excel 檔案的完整路徑和檔案名稱。
    5. [提供者字串] 方塊中,輸入 Excel 8.0 代表 Excel 2002、Excel 2000 或 Excel 97 活頁簿。
    6. 按一下 [確定] 以建立新的連結伺服器。
  4. 按一下以展開新的連結伺服器名稱,來展開它所包含的物件清單。
  5. 在新的連結伺服器名稱下方,按一下 [資料表]。請注意,您的工作表和已命名的範圍會出現在右窗格中。

使用預存程序將 Excel 資料來源設定為連結伺服器

您也可以使用系統預存程序 sp_addlinkedserver 將 Excel 資料來源設定為連結伺服器:
DECLARE @RC intDECLARE @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 valuesSET @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.CloseEnd 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.SmoImports Microsoft.SqlServer.Management.CommonPublic 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 SubEnd 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) 網站:如需有關如何啟用 [臨機操作分散式查詢] 選項的詳細資訊,請造訪下列 MSDN 網站:
內容

文章識別碼:306397 - 最後檢閱時間:02/16/2011 02:12:00 - 修訂: 6.4

  • kbsqldeveloper kbdatabase kbhowto kbjet KB306397
意見反應
ript" src="https://c.microsoft.com/ms.js" '="">