sqlserver中对应ORACLE DBLINK的功能链接服务器

郑全

ORACLE中，服务器之间分布式访问，通过DBLINK来实现，很方便，SQLSERVER也有类似的功能，叫链接服务器，下面看看具体的例子 ：



一、 创建链接服务器
1、使用sp_addlinkedserver来增加链接

EXEC sp_addlinkedserver@server='192.168.2.66',--被访问的服务器别名（习惯上直接使用目标服务器IP，或取个别名如：JOY）@srvproduct='',@provider='SQLOLEDB',@datasrc='192.168.2.66' --要访问的服务器

2、使用sp_addlinkedsrvlogin 来增加用户登录链接

EXEC sp_addlinkedsrvlogin'192.168.2.66', --被访问的服务器别名（如果上面sp_addlinkedserver中使用别名JOY，则这里也是JOY）'false',NULL,'sa', --帐号'test123' --密码

3、查看当前链接情况：

select * from sys.servers;

4、使用 sp_helpserver 来显示可用的服务器

Exec sp_helpserver

5、删除已经存在的某个链接

Exec sp_droplinkedsrvlogin 服务器别名,NullExec sp_dropserver 服务器别名

EXEC SP_DROPSERVER'ITSV' ,'DROPLOGINS'

可能会遇到的问题：
exec sp_dropserver 'JOY’执行失败，
报错信息：仍有对服务器 ‘JOY’ 的远程登录或链接登录。
解决方法：

exec sp_droplinkedsrvlogin 'JOY',nullexec sp_dropserver 'JOY'


6、查询示例

SELECT * FROM ITSV.数据库名.DBO.表名

举例(访问目标服务器上的数据库Music，查看其中表test的内容)：
如果建立链接时的别名是目标服务器IP，即192.168.2.66   ，则：

select * from [192.168.2.66].[Music].dbo.test

如果建立链接时的别名是JOY, 则：

select * from [JOY].[Music].dbo.test

7、导入示例

SELECT * INTO 表 FROM ITSV.数据库名.DBO.表名

二、连接远程/局域网数据(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'ITSV' ,'' , 'SQLOLEDB' ,'远程服务器名或IP地址'

–查询

SELECT * FROM OPENQUERY(ITSV ,'SELECT * FROM 数据库.DBO.表名')

–把本地表导入远程表

INSERT OPENQUERY(ITSV ,'SELECT * FROM 数据库.DBO.表名') SELECT * FROM 本地表


–更新本地表

UPDATE BSET B.列B=A.列B                                 
     FROM OPENQUERY(ITSV , '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 本地表