如何通过 SQL Server 链接服务器和分布式查询使用 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 链接服务器

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

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

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

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

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

使用存储过程配置 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 = 'MicrosoftJetOLEDB40'

SET @datasrc = 'c:/book1xls'

SET @provstr = 'Excel 80'

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 SQLDMOSQLServer

Dim ls As SQLDMOLinkedServer

Set s = New SQLDMOSQLServer

sConnect "(local)", "sa", "password"

Set ls = New SQLDMOLinkedServer

With ls

Name = "XLTEST_DMO"

ProviderName = "MicrosoftJetOLEDB40"

DataSource = "c:/book1xls"

ProviderString = "Excel 80"

End With

sLinkedServersAdd ls

sClose

End Sub

查询 Excel 链接服务器

将 Excel 数据源配置为链接服务器之后，您可以方便地通过“查询分析器”或其他客户端应用程序查询其数据。例如，为了检索存储在 Excel 文件 Sheet1 中的数据行，以下代码使用了您使用 SQL-DMO 配置的链接服务器： SELECT FROM XLTEST_DMOSheet1$

您还可以使用 OPENQUERY 以“passthrough”（传递）方式查询 Excel 链接服务器，如下所示： SELECT FROM OPENQUERY(XLTEST_DMO, 'SELECT FROM [Sheet1$]')

OPENQUERY 需要的第一个参数是链接服务器名称。工作表名称之间需要用分隔符分开，如上所示。

您还可以使用以下查询获得 Excel 链接服务器上所有可用表的列表： EXECUTE SP_TABLES_EX 'XLTEST_DMO'

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

'Data Source=c:/book1xls;Extended Properties=Excel 80')Sheet1$

注意，OPENROWSET 对第二个（“提供程序字符串”）参数使用一种非常规语法： SELECT FROM OPENROWSET('MicrosoftJetOLEDB40',

'Excel 80;Database=c:/book1xls', Sheet1$)

ActiveX 数据对象 (ADO) 开发人员按照常规应对 OPENROWSET 的第二个（“提供程序字符串”）参数使用的语法会 SELECT FROM OPENROWSET('MicrosoftJetOLEDB40',

'Data Source=c:/book1xls;Extended Properties=Excel 80', Sheet1$)

造成 Jet 提供程序出现以下错误：

Could not find installable ISAM

步骤：

1、给服务器设置一个固定的局域网IP

2、IIS的端口修改为8088

3、服务器的防火墙关闭或者开放8088端口

4、路由器做一下8088的端口影射

注意：登录的时候不管是局域网还是互联网，都需要在IP或者域名后面加上冒号和8088，例如

19216815：8088

1、

给服务器设置一个固定的局域网IP；

假设

通用Excel网络服务器的局域网IP是

19216815，如下图所示：

2、IIS的端口修改为88

对于动态拨号的用户来做，80端口已经被电信屏蔽，所以在配置服务器端口的时候，尽量不要用80端口。

3、服务器的防火墙关闭或者开放8088端口

4、路由器做一下8088的端口影射；

目的是把外界对88端口的访问转移到你指定的服务器上。下面我们以常用的

TP-Link为例子进行说明：打开IE浏览器进入路由器的设置界面，通常是http://19216811，依次展开左侧的菜单：转发规则

>

虚拟服务器，右侧点击[添加端口]，弹出的页面，输入服务器端口：8088，IP地址输入19216815，协议选择ALL勾选启用，然后保存即可。具体可以参考具体路由器的手册说明。

到目前为止，所有设置已经完成，已经可以正常使用了。

勤哲Excel服务器具备灵活的权限控制功能，可以按照角色和权限设置不同用户的访问权限。通过权限控制，企业可以保护敏感数据的安全性，确保只有授权人员才能访问和修改相关表格，提高数据的保密性和完整性。

不需要,excel只能简单完成小型数据库的作用65536256个单元格

把你的excel放到共享盘里面,设置为共享,则,所有人都可以同时操作

数据不会丢失，如果，几个人得操作有互相覆盖的，要看先后顺序和人工确认的最后数据。

Win10升级后，打开excel表格发现此表格不正常了，出现下图这样的stdloetld对话框，此现象意味着excel程序出了问题。

点击上图的确定后，出现如下界面：正在配置 office等内容，过了约1分钟左右excel表格的内容才能正常显示。

如果你卸载掉excel重新安装excel可能依然解决不了问题，很烦恼，在显示配置office的时候点击右上角的叉号关闭对话框出现如下界面，无论你是点击是或否结果是一样的，在等待约30秒后，表格内容可以打开了。我们要的不是这个，是需要表格的随时正常打开。

卸载excel表格也有学问，到控制面板的程序和功能栏目里找到office程序，点击删除之，然后安装个更高版本的office。有的电脑可能会出现卸载不成功的情况。

我们可以在软件管理程序里搜索系统优化大师安装后，利用这个软件来卸载，倘若该软件也无法卸载，会在该软件里看到excel在电脑里的路径，找到文件夹删除后再卸载，基本可以成功。

现在office2010,2013或者更高版本的软件基本都要付费，我们可以下载个免费的wps安装就可以使用excel了，不过原来电脑里的excel2007的表格可能无法打开，因为不兼容。

如果我们想要打开以前在excel2007状态下做的表格，可以下载个别的office2013,安装后一定可以打开excel2007表格的内容，因为该软件兼容它。

要实现这一功能，只需要简单的对域名进行设置即可，具体步骤如下：

进入要设置域名的管理控制中心，域名服务商一般都会提供域名管理帐号和密码等信息；

2找到域名管理中的菜单域名解析设置，域名设置中心都会有相关帮助说明可参阅； 

3新增域名解析条目，如下图所示；

上图以万网域名解析内容为例进行说明： 

记录类型：这里需要注意，如果通用Excel服务器所在网络有固定IP地址，那么这里就选择A记录。

如果通用Excel服务器所在网络有固定IP地址，那么这里就要选择上图所示的CNAME。 

主机记录：这里输入为通用Excel系统自定义的二级域名值，如：OA、ERP、或其它自定义内容。 

解析线路：这里默认即可。 

记录值：如果第一条选择的是A记录，主机记录这里就直接输入通用Excel服务器所在网络的固定IP地址。

第一条选择的是CNAME，主机记录这里就要输入跳转的域名地址了，比如花生壳动态解析域名。 

TTL值：这里默认即可。 

经过解析设置后，访问通用Excel服务器的地址就变成上述指定的自定义值+网站域名了