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

文章翻譯 文章翻譯
文章編號: 306397
全部展開 | 全部摺疊

在此頁中

結論

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 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(d=ide).aspx
如需有關如何啟用 [臨機操作分散式查詢] 選項的詳細資訊,請造訪下列 MSDN 網站:
http://msdn2.microsoft.com/zh-tw/library/ms189978(d=ide).aspx

屬性

文章編號: 306397 - 上次校閱: 2011年2月16日 - 版次: 6.4
關鍵字:?
kbsqldeveloper kbdatabase kbhowto kbjet KB306397
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com