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

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

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

您可以使用企业管理器、系统存储过程或 SQL-DMO（分布式管理对象）将 Excel 数据源配置为 SQL Server 链接服务器。在所有的这些情况中，您总需要设置以下四个属性：

• 链接服务器要使用的名称。 • 连接要使用的 OLE DB 提供程序。 • Excel 工作簿的数据源或完整路径和文件名。 • 将目标标识为 Excel 工作簿的提供程序字符串。默认情况下，Jet 提供程序需要 Access 数据库。

系统存储过程 sp_addlinkedserver 也需要 @srvproduct 属性，该属性可以是任何字符串值。

1. 在企业管理器中，单击以展开安全文件夹。 2. 右键单击链接服务器，然后单击新建链接服务器。 3. 在常规选项卡上，按照下列步骤操作： a. 在第一个文本框中，为该链接服务器键入任意名称。 b. 在服务器类型文本框中，单击其他数据源。 c. 在提供程序名称下拉列表框中，单击 Microsoft Jet 4.0 OLE DB 提供程序。 d. 在数据源文本框中，键入 Excel 文件的完整路径和文件名。 e. 在提供程序字符串文本框中，键入 Excel 8.0 代表 Excel 97、2000 或 2002 工作簿。 f. 单击确定以创建新的链接服务器。 4. 单击以展开此新链接服务器的名称，从而展开它所包含的对象列表。 5. 在新建链接服务器名称下，单击表。注意，工作表和命名区域将出现在右窗格中。

您还可以使用系统存储过程 sp_addlinkedserver 将 Excel 数据源配置为链接服务器： 如上所述，此存储过程另需一个任意字符串值作为 @srvproduct 参数的值，此参数在企业管理器配置中显示为“产品名称”。不使用 @location 和 @catalog 参数。

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

 

您可以使用 SQL 分布式管理对象从 Microsoft Visual Basic 或其他编程语言中将一个 Excel 数据源以编程方式配置为链接服务器。您必须也提供企业管理器配置中要求的那四个参数。

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\'

使用分布式查询来查询 Excel 您可以使用 SQL Server 分布式查询和 OPENDATASOURCE 或 OPENROWSET 函数临时查询很少访问的 Excel 数据源，如下所示：

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 提供程序出现以下错误：