2.5 链接服务器建立及其使用
链接服务器建立就是允许SQL Server对其他服务器上的OLE DB数据源进行访问操作，当然也包括其他远程服务器上的SQL Server数据源。
2.5.1 链接服务器简介
链接服务器泛指OLE DB 提供的程序和OLE DB数据源。
OLE DB提供的程序是管理特定数据源和与特定数据源进行交互的动态链接库（DLL）。OLE DB数据源标识可以通过OLE DB访问特定数据库。尽管通过链接服务器的定义所查询的数据源通常是数据库，但也存在适用于多种文件和文件格式的OLE DB提供程序，包括文本文件、电子表格数据和全文内容检索结果。
链接服务器具有以下优点。
（1）远程服务器访问
远程服务器访问是指用户通过本地SQL Server服务器能够访问到的网络上的其他SQL Server服务器。
一般情况下，用户对SQL Server数据库系统常用的访问方法是直接登录到所要访问的SQL Server服务器，然后根据个人权限访问服务器中不同的数据库对象。当网络中有多个SQL Server数据库服务器或实例时，用户要访问它们就需要分别登录，建立连接。这就要求用户在每个服务器上都有相应的登录标志和数据库用户名。
而采用链接服务器进行远程访问，用户利用本地服务器作为代理，只需登录到一个SQL Server服务器实例，然后通过它访问其他SQL Server实例。这时，用户不用登录到其他服务器即可执行它们中的存储过程，从而简化了用户登录操作。
（2）对整个企业内的异类数据源执行分布式查询、更新等事务
允许使用SELECT、INSERT、UPDATE和DELETE语句，也可以引用链接服务器上的视图、存储过程等数据对象。
（3）能够以相似的方式确定不同的数据源
表2-5所示为OLE DB对应需要的参数，在创建链接服务器时要根据不同的数据源对其参数进行设置。
表2-5 OLE DB对应需要的参数
2.5.2 创建链接服务器
创建链接服务器就是在SQL Server服务器上注册链接服务器的连接信息和数据源信息。注册以后，用户可以使用链接服务器唯一的逻辑名访问该数据源。创建链接服务器可以使用系统存储过程或使用企业管理器。
使用的系统存储过程是sp_addlinkedserver。
语法格式：
sp_addlinkedserver [@server = ] 'server'
[,[@srvproduct = ] 'product_name']
[,[ @provider = ] 'provider_name' ]
[,[@datasrc = ] 'data_source']
[,[@location = ] 'location']
[,[@provstr = ] 'provider_string']
[,[@catalog = ] 'catalog']
参数说明：
①[ @server = ] 'server'为要创建的链接服务器的名称或IP地址，server的数据类型为sysname，没有默认设置。
②[ @srvproduct = ] 'product_name'为要添加为链接服务器的OLE DB数据源的产品名称。product_name 的数据类型为 nvarchar（128），默认设置为NULL。
③[ @datasrc = ] 'data_source'为由OLE DB提供程序解释的数据源名称。data_source 的数据类型为nvarchar（4000），默认设置为NULL。
④[ @location = ] 'location'为OLE DB提供程序所解释的数据库的位置。location的数据类型为 nvarchar（4000），默认设置为NULL。
⑤[ @provstr = ] 'provider_string'为OLE DB 提供程序特定的连接字符串，它可标示唯一的数据源。provider_string的数据类型为 nvarchar（4000），默认设置为NULL。
⑥[ @catalog = ] 'catalog'为建立OLE DB提供程序的连接时所使用的目录。catalog的数据类型为sysname，默认设置为NULL。
返回代码值：0（成功）或1（失败）。
执行sp_addlinkedserver 以创建链接服务器，指定 Microsoft.Jet.OLEDB.4.0作为 provider_ name，并指定 Access的.mdb 数据库文件的完整路径名作为 data_source。.mdb 数据库文件必须驻留在服务器上。data_source 在服务器（而不是客户端）上进行计算，且路径必须是服务器上的有效路径。
假设本地服务器已经安装了Microsoft Access，并在其下建立了“教学管理”数据库，且数据库存放在 d:\samples下，数据库中建有若干表。下面的程序创建名为“MyAccess”的链接服务器。
USE master GO EXEC sp_addlinkedserver @server = 'MyAccess', @srvproduct = 'OLE DB Provider for Jet', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'd:\samples\教学管理.mdb' GO
2.5.3 创建链接服务器登录标志
建立链接服务器之后，为了使用户能够通过SQL Server服务器访问链接服务器，需要在链接服务器和SQL Server服务器之间建立登录标志映射关系，也就是建立SQL Server服务器中的登录标识在链接服务器上所对应的账户。
系统存储过程是sp_addlinkedsrvlogin。
语法格式：
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'useself' ] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ]
参数说明：
①[@rmtsrvname =] 'rmtsrvname'为应用登录映射的链接服务器名称。rmtsrvname 的数据类型为 sysname，没有默认设置。
②[@useself =] 'useself '为决定用于连接到远程服务器的本地登录。useself 的数据类型为varchar（8），默认设置为 TRUE。
③[@locallogin =] 'locallogin'为本地服务器上的登录名称。locallogin的数据类型为sysname，默认设置为 NULL。
④[@rmtuser =] 'rmtuser'为当useself为false时，用来连接远程用户的用户名，rmtuser 的数据类型为 sysname，默认设置为NULL。
⑤[@rmtpassword =] 'rmtpassword'为与rmtuser相关的密码。rmtpassword的数据类型为sysname，默认设置为NULL。
返回代码值：0（成功）或1（失败）。
注意：
·当用户登录到本地服务器，并执行分布式查询，以访问链接服务器上的表时，本地服务器必须登录到链接服务器上，代表该用户访问该表。
·不能从用户定义的事务中执行 sp_addlinkedsrvlogin。
·只有sysadmin和securityadmin固定服务器角色的成员才可以执行sp_addlinkedsrvlogin。
比如将上述创建的链接服务器MyAccess定义登录映射，使得本地用户zufe-mxh\meng（zufe-mxh是本地计算机名或IP地址，meng是登录本地机的用户名。一般计算机刚开始的用户名多为Administrator）可以访问名为MyAccess链接服务器，其中登录映射定义为用户名Admin（名字可以任意起），密码为NULL。下面的程序创建本地用户登录MyAccess链接服务器的标识。
sp_addlinkedsrvlogin 'MyAccess', false, 'zufe-mxh\meng', 'Admin', NULL GO
2.5.4 访问链接服务器
链接服务器是已定义到SQL Server 2000的虚拟服务器，其中包含了访问OLE DB数据源所需的全部信息。访问链接服务器上的数据对象，首先要链接服务器名称，使用系统存储过程sp_addlinkedserver定义，再使用sp_addlinkedsrvlogin将本地SQL Server登录映射为链接服务器中的登录，这两个过程的详细说明如上所述。下面是对不同类型数据源创建链接服务器并访问的实例。
在T-SQL语句中，指定数据库对象可以使用两种对象名：完全限定名和部分限定名。完全限定名是访问对象的全名，它包含4部分：服务器名、数据库名、所有者名和对象名。其格式如下：
ServerName.DataBaseName.OwnerUserName.TableName
部分限定名是未指出的部分，使用默认值，当省略指出部分时，圆点符号“.”不能省略。
从名为MyAccess的链接服务器中检索数据库“教学管理”中student表的所有行。这里只需使用部分名称结构。程序如下：
SELECT * FROM MyAccess...student
2.5.5 访问链接服务器的实例
【例2-1】创建链接服务器以访问SQL Server数据库。
创建一个名为LinkSQLSrvr的链接服务器，以便对运行于网络名称为zufe-mxh的服务器上的SQL Server实例进行操作。程序如下：
--创建链接服务器 sp_addlinkedserver @server = 'LinkSqlSrvr', @srvproduct = '', @provider ='SqlOLEDB', @datasrc = 'zufe-mxh' --必须是真正存在的服务器名称或IP地址 GO 或 sp_addlinkedserver N'LinkSqlSrvr', ' ', N'SqlOLEDB', N'zufe-mxh' GO
将本地登录sa的访问权限映射到名为LinkedSQLSrvr的链接服务器上的SQL Server授权登录meng（名字可以任意起）。程序如下：
sp_addlinkedsrvlogin 'LinkSqlSrvr', false, 'sa', 'meng', NULL GO
在链接服务器中访问SQL Server数据库的表时，必须使用完全限定名LinkedServerName. DataBaseName.OwnerUserName.TableName进行引用。下面是对SQL Server数据库master的sysobjects表的查询。
SELECT * FROM LinkSqlSrvr.master.dbo.sysobjects GO
【例2-2】创建链接服务器以访问Excel电子表格。
创建访问MyExcel电子表格的链接服务器，程序如下：
sp_addlinkedserver N'LinkExcel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'd:\samples\mysheet.xls', NULL, N'excel 5.0'
GO
定义登录映射，使得本地SQL Server用户sa或本地Windows用户zufe-mxh\meng可以访问链接服务器MyExcel上的\MySheet.xls电子表格，程序如下：
sp_addlinkedsrvlogin 'LinkExcel', false, 'sa', 'Admin', NULL
或
sp_addlinkedsrvlogin 'LinkExcel', false, 'zufe-mxh\meng', 'Admin', NULL GO
若要访问MyExcel链接服务器上的电子表格中的数据，需要将单元范围与表格名称相关联。下面的查询能够访问名为Sheet1的表格范围中的数据。这里也不能使用完全限定名称结构。
SELECT * FROM LinkExcel...sheet1$ GO
【例2-3】创建链接服务器以访问Oracle数据库实例。
将一个SQL*Net别名定义为OracleDB，创建链接服务器LinkOrclDB。程序如下：
sp_addlinkedserver 'LinkOrclDB', 'Oracle', 'MSDAORA', 'OracleDB' GO
通过Oracle登录名OrclUsr和密码OrclPwd将SQL Server登录Zufe-mxh\meng映射到链接服务器，程序如下：
sp_addlinkedsrvlogin 'LinkOrclDB', false, 'Zufe-mxh\meng', 'OrclUsr', 'OrclPwd' GO
每个Oracle数据库实例仅有一个名称为空的目录。Oracle链接服务器中的表必须使用4部分名称格式OracleLinked.ServerName.OwnerUserName.TableName进行引用。例如，以下SELECT语句引用Oracle用户mary在OrclDB链接服务器映射的服务器上所拥有的表sales。
SELECT * FROM LinkOrclDB..mary.sales GO