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

最新推荐文章于 2024-07-27 09:31:56 发布

tongdoudpj

最新推荐文章于 2024-07-27 09:31:56 发布

阅读量1.8k

点赞数

分类专栏：疑难杂症处理所 SQL、Oracle数据库研究文章标签：服务器 sql server excel microsoft properties 存储

疑难杂症处理所同时被 2 个专栏收录

44 篇文章

订阅专栏

SQL、Oracle数据库研究

41 篇文章

订阅专栏

本文介绍如何使用 SQL Server 通过链接服务器或分布式查询的方式查询 Microsoft Excel 数据源的方法。包括使用企业管理器、存储过程及 SQL-DMO 配置 Excel 链接服务器的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

概要

更多信息

作为链接服务器查询 Excel

使用企业管理器配置 Excel 链接服务器

使用存储过程配置 Excel 链接服务器

使用 SQL-DMO 配置 Excel 链接服务器

查询 Excel 链接服务器

使用分布式查询来查询 Excel

参考

概要

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

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

回到顶端

更多信息

作为链接服务器查询 Excel

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

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

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

使用企业管理器配置 Excel 链接服务器

在企业管理器中，单击以展开安全文件夹。

右键单击链接服务器，然后单击新建链接服务器。

在常规选项卡上，按照下列步骤操作：

a.	在第一个文本框中，为该链接服务器键入任意名称。
b.	在服务器类型文本框中，单击其他数据源。
c.	在提供程序名称下拉列表框中，单击 Microsoft Jet 4.0 OLE DB 提供程序。
d.	在数据源文本框中，键入 Excel 文件的完整路径和文件名。
e.	在提供程序字符串文本框中，键入 Excel 8.0 代表 Excel 97、2000 或 2002 工作簿。
f.	单击确定以创建新的链接服务器。

单击以展开此新链接服务器的名称，从而展开它所包含的对象列表。

在新建链接服务器名称下，单击表。注意，工作表和命名区域将出现在右窗格中。

使用存储过程配置 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 参数的值，此参数在企业管理器配置中显示为“产品名称”。不使用 @location 和 @catalog 参数。

使用 SQL-DMO 配置 Excel 链接服务器

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

Could not find installable ISAM.

回到顶端

参考

因为 SQL Server 链接服务器和分布式查询使用 OLE DB 提供程序，所以有关使用 ADO 处理 Excel 数据的一般原则和注意事项在此也适用。有关其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：

257819 (http://support.microsoft.com/kb/257819/) 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据