SQLServer LinkServer 链接服务器

Linked Server简介

我们日常使用SQL Server数据库时，经常遇到需要在实例Instance01中跨实例访问Instance02中的数据。例如在做数据迁移时，如下语句:

insert into Instance01.DB01.dbo.Table01
   select * from Instance02.DB01.dbo.Table01

普通情况下，这样做是不允许的，因为SQL Server默认不可以跨实例访问数据。解决方案是使用存储过程sp_addlinkedserver进行实例注册。

链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。在创建某一链接服务器后，可对该服务器运行分布式查询，并且查询可以联接来自多个数据源的表。如果链接服务器定义为 SQL Server 实例，则可执行远程存储过程。链接服务器具有以下优点：

• 能够访问 SQL Server之外的数据。
• 能够对企业内的异类数据源发出分布式查询、更新、命令和事务。
• 能够以相似的方式确定不同的数据源。

Linked Server组件

链接服务器定义指定了下列对象：

• OLE DB 访问接口
• OLE DB 数据源

“OLE DB 访问接口” 是管理特定数据源并与其交互的 DLL。 “OLE DB 数据源” 标识可通过 OLE DB 访问的特定数据库。 虽然通过链接服务器定义查询的数据源通常是数据库，但 OLE DB 访问接口对各种文件和文件格式仍可用。 这些文件和文件格式包括文本文件、电子表格数据和全文内容搜索的结果。

Microsoft SQL Server本机客户端 OLE DB 访问接口 (PROGID: SQLNCLI11) 是 SQL Server 的正式 OLE DB 访问接口。

通常，链接服务器用于处理分布式查询。 当客户端应用程序通过链接服务器执行分布式查询时， SQL Server 将分析命令并向 OLE DB 发送请求。 为使数据源能通过链接服务器返回数据，该数据源的 OLE DB 访问接口 (DLL) 必须与 SQL Server 的实例位于同一服务器上

Linked Server的安全性

当用户登录到本地服务器并执行分布式查询，以访问链接服务器上的表时，本地服务器必须登录链接服务器上，代表该用户访问该表。使用存储过程sp_addlinkedsrvlogin来指定本地服务器用于登录链接服务器的登录凭据。
例如，使用远程密码 8r4li034j7$ 为链接服务器 S1 建立了一个从本地登录名 U1 到远程登录名 U2 的映射（如在SQL Server中使用sa账户登录到远程oracle服务器的sys账户）。在本地登录名 U1 执行访问链接服务器 S1 中存储的表的分布式查询时，如果 SQL Server 连接到链接服务器 S1，则将 U2 和 8r4li034j7$ 分别作为用户 ID 和密码进行传递。
本地服务器上的所有登录和链接服务器上的远程登录之间的默认映射通过执行 sp_addlinkedserver 自动创建。默认映射表示，当代表本地登录连接到链接服务器时，SQL Server 使用本地登录的用户凭据。这相当于在链接服务器的 @useself 设置为 true 的情况下执行 sp_addlinkedsrvlogin，无需指定本地用户名。
创建或更新 SQL Server 本地实例上的登录名与远程服务器中安全帐户之间的映射语法如下：

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 
     [ , [ @locallogin = ] 'locallogin' ] 
     [ , [ @rmtuser = ] 'rmtuser' ] 
     [ , [ @rmtpassword = ] 'rmtpassword' ]

• @rmtsrvname：应用登录映射的链接服务器的名称。
• @useself：确定是否通过模拟本地登录名或显式提交登录名和密码来连接到 rmtsrvname。默认值为 TRUE。
  值为 TRUE 指定登录名使用其自己的凭据来连接到rmtsrvname，rmtuser和rmtpassword自变量被忽略。 FALSE指定rmtuser和rmtpassword参数用于连接到rmtsrvname。 如果rmtuser和rmtpassword也是设置为 NULL，任何登录名或密码用于连接到链接服务器。
• @locallogin：指要本地哪个账户登录到远程服务器。如果不为 NULL， locallogin可以是一个SQL Server登录或 Windows NT用户。
• @rmtuser：用于连接到远程服务器的登录名。
• @rmtpassword：用于连接到远程服务器的密码。

【示例】

A. 使用各自的用户凭据将所有本地登录连接到链接服务器
以下示例将创建一个映射，以确保所有到本地服务器的登录都使用其各自的用户凭据连接到链接服务器 Accounts。

EXEC sp_addlinkedsrvlogin 'Accounts'
或
EXEC sp_addlinkedsrvlogin 'Accounts', 'true'

B. 使用不同的用户凭据将特定的登录连接到链接服务器
以下示例将创建一个映射，以确保 Windows 用户 DomainMary 使用登录名 MaryP 和密码 d89q3w4u 连接到链接服务器 Accounts。

EXEC sp_addlinkedsrvlogin
        'Accounts',
        'false',
        'DomainMary', 
        'MaryP', 
        'd89q3w4u'

C. 不使用任何用户凭据将所有本地登录连接到链接服务器
此示例创建一个映射，以确保所有到本地服务器的登录都连接到链接服务器 mydb，而不使用登录或密码（mydb 不需要登录或密码）。

EXEC sp_addlinkedsrvlogin 'mydb', 'false', NULL, NULL, NULL
or
EXEC sp_addlinkedsrvlogin 'mydb', 'false'

D. 将特定的登录连接到 Excel 电子表格（链接服务器）

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:MyDataDistExcl.xls',
   NULL,
   'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

创建Linked Server

（1）在SSMS中创建链接服务器

1. 在 SQL Server Management Studio 中，打开对象资源管理器，展开“Server Object”，右键单击“Linked Servers”，然后单击“New Linked Servers”。

   

2. 在“常规”页上的“链接服务器”框中，键入您链接到的 SQL Server 实例的名称。

   如果该 SQL Server 实例是默认实例，则输入承载 SQL Server 实例的计算机的名称。如果该 SQL Server 是命名实例，则输入计算机名称和实例名称，例如 AccountingSQLExpress。

3. 在“服务器类型”区域中，选择 SQL Server 以便指示该链接服务器是 SQL Server 的另一个实例。

4. 在“安全性”页上，指定在原始 SQL Server 连接到链接服务器时将使用的安全上下文。在通过使用其域登录名连接用户的域环境中，选择“使用登录名的当前安全上下文建立连接”通常是最佳选择。在用户通过使用 SQL Server 登录名连接到原始 SQL Server 时，最佳选择通常是选择“通过使用此安全上下文”，然后提供在链接服务器上进行身份验证时所必需的凭据。
5. 单击“确定”。

（2）使用 Transact-SQL创建链接服务器

sp_addlinkedserver [@server=] 'server'
     [ , [ @srvproduct= ] 'product_name']
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]

参数说明

• @server：链接服务器别名（习惯上直接使用目标服务器IP，或取个别名如：Test，反正想取什么名就取什么）
• @srvproduct：要添加为链接服务器的 OLE DB 数据源的产品名称。 如果为 SQL Server，则不必指定provider_name、data_source、location、provider_string 和 catalog。
• @provider：与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。但如果忽略 provider_name，则使用 SQLNCLI。
• @datasrc：链接服务器名，也就是与指定链接服务器对应的OLE DB数据源属性。
• @location：与指定链接服务器对应的OLE DB位置属性。
• @catalog：与指定链接服务器对应的OLE DB目录属性。

@xxx这些名词在写的时候是可以省略的。

【注释】
下表显示为能通过 OLE DB 访问数据源而建立链接服务器的方法。对于特定的数据源，可以使用多种方法为其设置链接服务器；该表中可能有多行适用于一种数据源类型。该表还显示了用于设置链接服务器的 sp_addlinkedserver 参数值。

• “任何”指产品名称可以任意。
• 如果未指定访问接口名称或将 SQL Server 指定为产品名称，则 Microsoft SQL Server Native Client OLE DB 访问接口将是用于 SQL Server 的访问接口。即使指定了较早版本的访问接口名称 SQLOLEDB，在保存到目录时该名称也将改为 SQLNCLI。

【示例】

A. 访问MSSQL

1. 在查询编辑器中，输入以下 Transact-SQL 命令以便链接到名为 SRVR002ACCTG 的 SQL Server 实例：

   USE [master]
   GO
   EXEC master.dbo.sp_addlinkedserver 
    @server = N'SRVR002ACCTG', 
    @srvproduct = N'SQL Server' ;
   GO

2. 执行以下代码，以便将链接服务器配置为使用正在使用链接服务器的登录名的域凭据。

   EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'SRVR002ACCTG', 
    @locallogin = NULL , 
    @useself = N'True' ;
   GO

3. 跟进：在创建链接服务器后采取的步骤

   查询的数据库格式为：

   [别名].[数据库名].[架构名].[表名]

   插入数据库格式为：

   insert [数据库名].[架构名].[表名] select * from [别名].[数据库名].[架构名].[表名]

   select * into [数据库名].[架构名].[新表名] from [别名].[数据库名].[架构名].[表名]

   • 执行下面的代码，测试与链接服务器的连接。以下示例返回链接服务器上数据库的名称。

     SELECT name FROM [SRVR002ACCTG].master.sys.sysdatabases;

   • 使用由四部分组成的名称引用链接服务器上的对象。执行以下代码，以便返回本地服务器上所有登录名的列表及其在链接服务器上的匹配登录名。

     -- sys.server_principals可以查询登入名拥有的服务器角色
     SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
     FROM master.sys.server_principals AS local
     LEFT JOIN [SRVR002ACCTG].master.sys.server_principals AS linked
     ON local.name = linked.name;

B. 访问ACCESS数据库

1. Microsoft.Jet.OLEDB.4.0 访问接口连接到使用 2002-2003 格式的 Microsoft Access 数据库。 下面的示例将创建一个名为 SEATTLE Mktg 的链接服务器。

   EXEC sp_addlinkedserver   
   @server = N'SEATTLE Mktg',   
   @provider = N'Microsoft.Jet.OLEDB.4.0',   
   @srvproduct = N'OLE DB Provider for Jet',  
   @datasrc = N'C:MSOfficeAccessSamplesNorthwind.mdb';  
   GO

   @datasrc='ServiceName'，即数据源的格式如下：
   （1）盘符:路径文件名
   （2）网络名共享名文件名 (网络版本)

2. 若要访问Access数据库中的数据，使用以下的查询格式：

   select * from 别名...表名

   C. 访问Oracle数据库

   访问前需要SQL SERVER服务器中安装ORACLE客户端。

3. 创建链接服务器

   EXEC sp_addlinkedserver
        @server ='ora', -- 要创建的链接服务器别名
        @srvproduct='Oracle', --产品名称    
        @provider='MSDAORA', 
        @datasrc='oracle客户端设置的本地net服务名'   --数据源
   GO

   @datasrc可以在oracleora92 etworkadmin nsnames.ora文件中查看，文件中的SERVICE_NAME就是服务名。

4. 若要访问Oracle数据库中的数据，使用以下的查询格式：

   -- 四部分名称全部用大写，因为在Oracle的数据字典中为大写
   SELECT * FROM 链接服务器别名..ORACLE用户名.表名(视图)

5. 若要执行Oracle数据库中的存储过程，使用以下的查询：

   SELECT * FROM OPENQUERY(别名, ’exec 用户名.存储过程名’)

D. 访问EXCEL

1. 若要创建链接的服务器定义使用MicrosoftOLE DB Provider for Jet 访问 Excel 电子表格中 1997年-2003年格式中，首先创建的命名的区域在 Excel 中通过指定要选择的 Excel 工作表的行和列。 这样，可以在分布式查询中将此范围的名称引用为表名称。

   EXEC sp_addlinkedserver 'ExcelSource',  
   'Jet 4.0',  -- 产品名称
   'Microsoft.Jet.OLEDB.4.0',  -- provider
   'c:MyDataDistExcl.xls',  
   NULL,  
   'Excel 5.0';  
   GO

   若要访问 Excel 电子表格中的数据，请将单元范围与名称相关联。 以下查询通过使用先前设置的链接服务器，将指定的命名范围 SalesData 作为表来访问。

   SELECT * FROM ExcelSource...SalesData;

2. 如果 SQL Server 在可以访问远程共享的域帐户下运行，则可以使用 UNC 路径来代替映射驱动器。

   EXEC sp_addlinkedserver 'ExcelShare',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   '\MyServerMyShareSpreadsheetsDistExcl.xls',  -- UNC 路径
   NULL,  
   'Excel 5.0';

如何在SSMS查看Linked Server？
在SSMS的Server Object——>Linked Servers可以找到本数据库所有的数据库链接，如下图：

此外，通过执行Programmability——>Store Procedures——>System Stored Procedures——>sp_linkedservers 这个存储过程也可以查询到当前数据库的所有linked server。如下图：

OpenQuery

有时候我们希望在一个sqlserver下访问另一个sqlserver数据库上的数据，或者访问其他oracle数据库上的数据，要想完成这些操作，我们首要的是创建数据库链接（Linked Server）。 数据库链接能够让本地的一个sqlserver登录用户映射到远程的一个数据库服务器上，并且像操作本地数据库一样。OpenQuery 就是SQL Server用来与其他Server交互的一种技术，其他数据库在OpenQuery 表达式中是以Linked Server 存在的。
OPENQUERY 可以在查询的 FROM 子句中引用，就好象它是一个表名。OPENQUERY 也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。

语法

OPENQUERY ( linked_server ,'query' )

• linked_server ：表示链接服务器名称的标识符。
• query：在链接服务器中执行的查询字符串。 该字符串的最大长度为 8 KB。

示例
A. 执行 UPDATE 传递查询

UPDATE OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName') SET role_id = 'ForTest' WHERE id = 3

可以简写为：

UPDATE MSSQLSvr.DataBaseName.dbo.tableName SET role_id = 'ForTest' WHERE id = 3;

B. 执行 INSERT 传递查询

INSERT INTO OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName WHERE 1>1') VALUES(3,6,'ANDY');

可以简写为：

INSERT INTO MSSQLSvr.DataBaseName.dbo.tableName VALUES(3,6,'ANDY');

C. 执行 DELETE 传递查询

DELETE OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName') WHERE id IN (1,3);

可以简写为：

DELETE MSSQLSvr.DataBaseName.dbo.tableName WHERE id IN (1,3);

D. 执行SELECT传递查询

SELECT 1 FROM OPENQUERY(OracleSvr,'SELECT * FROM stuMan.person WHERE id > 10');

E. 执行存储过程

SELECT EXECUTE testProcedure(isnull(name,' ')) 
   FROM OPENQUERY(OracleSvr,'SELECT name FROM stuMan.person')

备注
（1）OPENQUERY 不接受参数变量，也就是说：

DELETE OPENQUERY (linked_server, 'SELECT id FROM msgs WHERE id=' + @id);

是不正确的，但可以这样做：

DELETE OPENQUERY (linked_server, 'SELECT id FROMmsgs') WHERE id=@id); 。

（2）若没有参数变量，最好把条件表达式卸载OPENQUERY如里面，因为在linked_server执行完筛选才RETURN的，TABLE的数据量越大效果越明显。如下面的SQL语句：

select * from openquery( OracleSvr ,'select * from kxx_file  ') 
     where kxx07='2009-10-08'

该语句查询耗时18s；很明显RETURN的数据相当大，再进行筛选；而

select * from openquery( OracleSvr ,
  'select * from kxx_file where kxx07={ ts ''2009-10-08 00:00:00.000''} ')

该语句查询耗时3s。
（3）T-SQL 中，INSERT 可以省略 INTO，DELETE 也可以省略 FROM。
（4）我们在对表进行查询时，通常都会有传入的参数。在OpenQuery中使用参数和我们平时写的数据库语句会不会有所不同呢？对于Int类型的参数，没有什么不同。但是，如果是字符类型的参数，比如，你要在Openquery中执行如下的数据库语句：

select * from tbl_emp Where empName = 'leo'

那么我们就必须对这个参数进行转义，单变双实现字符转义。如下：

select * from OpenQuery（[linkedServerName],'Select * from table1 where empName = ''leo''')

即我们必须对''进行转义，所以'' 就变成了''''。

T-SQL&&SQL

作者：zoyoto
链接：http://www.jianshu.com/p/6abd69f44463
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。