如何通过SQLServer链接服务器和分布式查询使用Excel

SQL Server 支持到其他 OLE DB 数据源的永久性连接或临时连接。永久性连接叫做链接服务器；为单个查询建立的临时连接叫做分布式查询。

Microsoft Excel 工作簿是一种可通过 SQL Server 以这种方式查询的 OLE DB 数据源。本文介绍了将 Excel 数据源配置为链接服务器所需的语法，以及使用分布式查询来查询 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

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 链接服务器 将 Excel 数据源配置为链接服务器之后，您可以方便地通过“查询分析器”或其他客户端应用程序查询其数据。例如，为了检索存储在 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\'

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 数据对象 (ADO) 开发人员按照常规应对 OPENROWSET 的第二个（“提供程序字符串”）参数使用的语法会

SELECT * FROM OPENROWSET(\'Microsoft.Jet.OLEDB.4.0\', \'Data Source=c:/book1.xls;Extended Properties=Excel 8.0\', Sheet1$) 造成 Jet 提供程序出现以下错误：