SQL Server 使用链接服务器

SQL Server(2005测试通过) 使用链接服务器，通常需要在A库的环境下的sql命令窗口中去对另一个B库进行操作，这时候就要用到链接服务器，很方便，相当于Oracle中的数据链，不过我个人感觉没Oracle里的数据链方便。

 

方法一：
EXEC sp_addlinkedserver 'LinkServer',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=192.168.0.212;UID=sa;PWD=sa;'


SELECT * FROM  LinkServer.ty_DB.dbo.ty_DB_Tinfo t WHERE t.Tinfo_fid BETWEEN 4951590 AND 4951990

DROP VIEW dbo.ty_DB_Tinfo

CREATE VIEW [dbo].[ty_DB_Tinfo]
AS  SELECT  t.c_id,
            t.Tinfo_fid,
            t.c_title,
            t.c_position,
            t.c_event_desc,
            t.c_abs_x,
            t.c_abs_y,
            t.c_db_create_date
    FROM    LinkServer.ty_DB.dbo.ty_DB_Tinfo t
    WHERE   t.Tinfo_fid BETWEEN 4951590 AND 4951990


SELECT * FROM dbo.ty_DB_Tinfo

UPDATE LinkServer.ty_DB.dbo.ty_DB_Tinfo 
SET c_event_desc='乱帖小广告'
WHERE Tinfo_fid=4951595

方法二：
--创建链接服务器
exec sp_addlinkedserver  'MyServer','','SQLOLEDB','远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'MyServer','false',null,'用户名','密码'

--查询示例
select * from MyServer.数据库名.dbo.表名

--导入示例
select * into 表 from MyServer.数据库名.dbo.表名

--以后不再使用时删除链接服务器
exec sp_dropserver 'MyServer','droplogins'

--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset

--查询示例
select * from openrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)

--生成本地表
select * into 表 from openrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)

--把本地表导入远程表
insert openrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
select *from 本地表

--更新本地表
update b
set b.列A=a.列A
 from openrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1

--openquery用法需要创建一个连接

--首先创建一个连接创建链接服务器
exec sp_addlinkedserver  'MyServer','','SQLOLEDB','远程服务器名或ip地址'
--查询
select *
FROM openquery(MyServer, 'SELECT *  FROM 数据库.dbo.表名')
--把本地表导入远程表
insert openquery(MyServer, 'SELECT *  FROM 数据库.dbo.表名')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(MyServer, 'SELECT * FROM 数据库.dbo.表名') as a 
inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset
SELECT   *
FROM   opendatasource('SQLOLEDB', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource('SQLOLEDB', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').数据库.dbo.表名
select * from 本地表 

参考 MSDN：

http://technet.microsoft.com/zh-cn/library/ms190479.aspx

sp_addlinkedserver (Transact-SQL)

 

创建链接服务器。链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。在使用 sp_addlinkedserver 创建链接服务器后，可对该服务器运行分布式查询。如果链接服务器定义为 SQL Server 实例，则可执行远程存储过程。

Transact-SQL 语法约定

 语法

 

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

 参数

[ @server = ] 'server'

要创建的链接服务器的名称。server 的数据类型为 sysname，没有默认值。

[ @srvproduct = ] 'product_name'

要添加为链接服务器的 OLE DB 数据源的产品名称。product_name 的数据类型为 nvarchar(128)，默认值为 NULL。如果为 SQL Server，则不必指定 provider_name、data_source、location、provider_string 和 catalog。

[ @provider = ] 'provider_name'

与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。对于当前计算机中安装的指定 OLE DB 访问接口，provider_name 必须唯一。provider_name 的数据类型为 nvarchar(128)，默认值为 NULL；但如果忽略 provider_name，则使用 SQLNCLI。（使用 SQLNCLI 并且 SQL Server 将重定向到 SQL Server Native Client OLE DB 访问接口的最新版本。）OLE DB 访问接口应以指定的 PROGID 在注册表中注册。

[ @datasrc = ] 'data_source'

由 OLE DB 访问接口解释的数据源的名称。data_source 的数据类型为 nvarchar(4000)。data_source 作为 DBPROP_INIT_DATASOURCE 属性传递以初始化 OLE DB 访问接口。

[ @location = ] 'location'

由 OLE DB 访问接口解释的数据库的位置。location 的数据类型为 nvarchar(4000)，默认值为 NULL。location 作为 DBPROP_INIT_LOCATION 属性传递以初始化 OLE DB 访问接口。

[ @provstr = ] 'provider_string'

OLE DB 访问接口特定的连接字符串，它可标识唯一的数据源。provider_string 的数据类型为 nvarchar(4000)，默认值为 NULL。provstr 或传递给 IDataInitialize 或设置为 DBPROP_INIT_PROVIDERSTRING 属性以初始化 OLE DB 访问接口。

当针对 SQL Server Native Client OLE DB 访问接口创建链接服务器时，可以使用 SERVER 关键字来指定实例，即使用 SERVER=servername\instancename 指定特定 SQL Server 实例。servername 是运行 SQL Server 的计算机的名称，instancename 是用户要连接的特定 SQL Server 实例的名称。

注意：

若要访问镜像数据库，则连接字符串必须包含数据库名称。该名称是数据访问接口启用故障转移尝试所必需的。可以在 @provstr 或 @catalog 参数中指定数据库。此外，连接字符串还可以提供故障转移伙伴名称。有关详细信息，请参阅 建立到数据库镜像会话的初始连接。

 

 

[ @catalog = ] 'catalog'

与 OLE DB 访问接口建立连接时所使用的目录。catalog 的数据类型为 sysname，默认值为 NULL。catalog 作为 DBPROP_INIT_CATALOG 属性传递以初始化 OLE DB 访问接口。在针对 SQL Server 实例定义链接服务器时，目录指向链接服务器映射到的默认数据库。

 返回代码值

0（成功）或 1（失败）

 结果集

无。

sp_addlinkedsrvlogin (Transact-SQL)

 

创建或更新 SQL Server 本地实例上的登录名与远程服务器中安全帐户之间的映射。

Transact-SQL 语法约定

 语法

 

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

 参数

[ @rmtsrvname = ] 'rmtsrvname'

应用登录映射的链接服务器的名称。rmtsrvname 的数据类型为 sysname，没有默认值。

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

确定是否通过模拟本地登录名或显式提交登录名和密码来连接到 rmtsrvname。数据类型为 varchar(8)，默认值为 TRUE。

值为 TRUE 指定登录名使用自己的凭据连接到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 参数。FALSE 指定 rmtuser 和 rmtpassword 参数用于连接到指定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也设置为 NULL，则不使用登录名或密码来连接链接服务器。

[ @locallogin = ] 'locallogin'

本地服务器上的登录。locallogin 的数据类型为 sysname，默认值为 NULL。NULL 指定此项应用于连接到 rmtsrvname 的所有本地登录。如果不为 NULL，则 locallogin 可以是 SQL Server 登录或 Windows 登录。对于 Windows 登录来说，必须以直接的方式或通过已被授权访问的 Windows 组成员身份授予其访问 SQL Server 的权限。

[ @rmtuser = ] 'rmtuser'

当 @useself 为 FALSE 时，用于连接到 rmtsrvname 的远程登录名。当远程服务器是不使用 Windows 身份验证的 SQL Server 实例时，rmtuser 是一个 SQL Server 登录名。rmtuser 的数据类型为 sysname，默认值为 NULL。

[ @rmtpassword = ] 'rmtpassword'

与 rmtuser 关联的密码。rmtpassword 的数据类型为 sysname，默认值为 NULL。

 返回代码值

0（成功）或 1（失败）