如何通過(guò)SQLServer鏈接服務(wù)器和分布式查詢使用Excel

SQL Server 支持到其他 OLE DB 數(shù)據(jù)源的永久性連接或臨時(shí)連接。永久性連接叫做鏈接服務(wù)器；為單個(gè)查詢建立的臨時(shí)連接叫做分布式查詢。

Microsoft Excel 工作簿是一種可通過(guò) SQL Server 以這種方式查詢的 OLE DB 數(shù)據(jù)源。本文介紹了將 Excel 數(shù)據(jù)源配置為鏈接服務(wù)器所需的語(yǔ)法，以及使用分布式查詢來(lái)查詢 Excel 數(shù)據(jù)源所需的語(yǔ)法。

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

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 查詢 Excel 鏈接服務(wù)器 將 Excel 數(shù)據(jù)源配置為鏈接服務(wù)器之后，您可以方便地通過(guò)“查詢分析器”或其他客戶端應(yīng)用程序查詢其數(shù)據(jù)。例如，為了檢索存儲(chǔ)在 Excel 文件 Sheet1 中的數(shù)據(jù)行，以下代碼使用了您使用 SQL-DMO 配置的鏈接服務(wù)器：

SELECT * FROM XLTEST_DMO...Sheet1$ 您還可以使用 OPENQUERY 以“passthrough”（傳遞）方式查詢 Excel 鏈接服務(wù)器，如下所示：

SELECT * FROM OPENQUERY(XLTEST_DMO, \'SELECT * FROM [Sheet1$]\') OPENQUERY需要的第一個(gè)參數(shù)是鏈接服務(wù)器名稱。工作表名稱之間需要用分隔符分開(kāi)，如上所示。

您還可以使用以下查詢獲得 Excel 鏈接服務(wù)器上所有可用表的列表：

EXECUTE SP_TABLES_EX \'XLTEST_DMO\'

SELECT * FROM OPENDATASOURCE(\'Microsoft.Jet.OLEDB.4.0\', \'Data Source=c:/book1.xls;Extended Properties=Excel 8.0\')...Sheet1$ 注意，OPENROWSET 對(duì)第二個(gè)（“提供程序字符串”）參數(shù)使用一種非常規(guī)語(yǔ)法：

SELECT * FROM OPENROWSET(\'Microsoft.Jet.OLEDB.4.0\', \'Excel 8.0;Database=c:/book1.xls\', Sheet1$) ActiveX 數(shù)據(jù)對(duì)象 (ADO) 開(kāi)發(fā)人員按照常規(guī)應(yīng)對(duì) OPENROWSET 的第二個(gè)（“提供程序字符串”）參數(shù)使用的語(yǔ)法會(huì)

SELECT * FROM OPENROWSET(\'Microsoft.Jet.OLEDB.4.0\', \'Data Source=c:/book1.xls;Extended Properties=Excel 8.0\', Sheet1$) 造成 Jet 提供程序出現(xiàn)以下錯(cuò)誤：