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

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

 

http://hi.baidu.com/tenglong100/blog/item/8273abd0f9cc08da572c84cd.html

 

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 链接服务器

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

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

您还可以使用系统存储过程 sp_addlinkedserver 将 Excel 数据源配置为链接服务器：

DECLARE @RC intDECLARE @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 valuesSET @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.CloseEnd 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.

 

这篇文章中的信息适用于:

• Microsoft SQL Server 7.0 标准版
• Microsoft SQL Server 2000 标准版

 

 

类别：sql server技术文档 | | 添加到搜藏 | 分享到i贴吧 | 浏览(64) | 评论 (0)

 

上一篇：不错的切换图片效果，在FF下有点...    下一篇：SQL Server与Excel、Access数据...

 

 

相关文章：

•SQL SERVER 与ACCESS,EXCEL的数...　　　　　　　　　•从SQL Server中导入/导出 Excel ...•SQL SERVER 和EXCEL的数据导入导...　　　　　　　　　•Excel数据导入到Access,Sql Serv...•高效地将EXCEL导入SQL SERVER　　　　　　　　　•SQL SERVER,ACCESS,EXCEL数据转...•将sql server中的数据倒入Excel(...　　　　　　　　　•SP_addlinkedserver 小结 (oracl...•Sql server; Access;Excel　　　　　　　　　•Excel数据导入Sql Server出现Nul...更多>>