SQL创建链接服务器(脚本)

适用场景：对远程的DB进行操作。

2000与2005对比：在SQL Server 2000版本中也有链接远程DB的SQL，但是功能比较弱，扩展性差，支持的查询比较简单。而SQL Server 2005版本的SSMS中已经有了 服务器对象->链接服务器 的功能点，用户首先创建一个远程DB的链接对象，之后就可以像本地表一样执行表的DML了。

创建步骤：在SQL Server 2005版本打开SSMS，服务器对象->链接服务器->右击 新建链接服务器，在图2中是一种设置方式，也有其它的设置方式

注意事项： 在MSSQL2005中Rpc的默认设置为True， 右键点击远程链接->属性->服务器选项->Rpc和Rpc Out，这两个值需要设置为True。

但在MSSQL2008下不能直接修改链接服务器 'ETV2_LINK' 的RPC配置成TURE，可以通过语句修改如下：

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'rpc out', @optvalue=N'true'
GO

 

生成脚本：如果已经通过操作界面生成了 'ETV2_LINK' 的链接服务器，那么我们如果需要把它移植到其它数据库（部署、更新）的时候，就可以通过下面的方法来生产SQL脚本，你也可以通过修改SQL脚本来快速新建或修改链接服务器，比如修改@server链接服务器名称，修改@datasrc远程链接的数据库对象。 

SQL Server 2005生成远程链接对象的SQL脚本：

/****** 对象:  LinkedServer [ETV2_LINK]    脚本日期: 09/08/2010 17:36:11 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ETV2_LINK', @srvproduct=N'ETV2_LINK', @provider=N'SQLNCLI', @datasrc=N'BWA035\BWA035_2K5'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ETV2_LINK', @optname=N'use remote collation', @optvalue=N'true'

 

使用：假设已经创建了名为ETV2_LINK的远程链接对象，那么你就可以像下面的方式来使用这个对象操作远程DB。

使用场景1： 查询ETV2_LINK这个远程链接对象的[etV2_Online]数据库中VisiteLog_20100629表的数据。模板形如：Select * From [链接服务器名].[远程数据库名].[所有者].[表名]

--查询远程DB表TableName
select * from ETV2_LINK.[etV2_Online].dbo.VisiteLog_20100629

 

使用场景2： 判断ETV2_LINK这个远程链接对象的[etV2_Online]数据库中是否存在名为VisiteLog_20100629的表。

--注意：是sys.objects不是sysobjects
--判断远程用户是否存在某张表
IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N'VisiteLog_20100629' AND type in (N'U'))
BEGIN
    --逻辑处理
    print '存在表'
END

 

使用场景3： 判断远程DB的[etV2_Online]数据库中是否存在名为VisiteLog_20100629的表。只不过这个表名是参数化的，可以通过传入的参数进行判断。这里只是简单的设置变量的值并使用OUT来返回变量。 

--判断远程用户是否存在某张表（参数化表名），返回变量
DECLARE @IsExistTable VARCHAR(10)
DECLARE @Tablename VARCHAR(50)
DECLARE @sqlString NVARCHAR(4000)
SET @IsExistTable = 'False'
SET @Tablename = 'VisiteLog_'+convert(varchar(9),getdate()-1,112) --例如VisiteLog_20100629
SET @sqlString = 
'IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N'''+@Tablename+''' AND type in (N''U''))
    set @IsExistTableOUT =''True'''
EXEC sp_executesql @sqlString,N'@IsExistTableOUT varchar(10) OUTPUT',@IsExistTableOUT=@IsExistTable OUTPUT

IF (@IsExistTable = 'True')--存在
BEGIN
    --逻辑处理
    print '存在表'
END

 

补充： SQL Server 2000版本连接远程服务器的SQL脚本，更多相关脚步可以参考：在T-SQL语句中访问远程数据库(openrowset/opendatasource/openquery) 

--方法1：
select *  from openrowset('SQLOLEDB','server=192.168.0.67;uid=sa;pwd=password','SELECT * FROM BCM2.dbo.tbAppl')

--方法2：
select *  from openrowset('SQLOLEDB','192.168.0.67';'sa';'password','SELECT * FROM BCM2.dbo.tbAppl') 

------------------------------------------------------------------------------------------------------------------

SP_addlinkedserver 小结 （oracle,sql server,access,excel）

    

/////////////////////////////SQL SERVER////////////////////////////

Exec sp_droplinkedsrvlogin DBVIP,Null

Exec sp_dropserver DBVIP

EXEC sp_addlinkedserver

      @server='DBVIP',--被访问的服务器别名

      @srvproduct='',

      @provider='SQLOLEDB',

      @datasrc='Server2'   --要访问的服务器

 

EXEC sp_addlinkedsrvlogin 

     'DBVIP', --被访问的服务器别名

     'false', 

     NULL, 

     'sa', --帐号

     'thankyoubobby' --密码

 

Select   *   from DBVIP.pubs.dbo.orders   

 

/////////////////////////////ORACLE////////////////////////////

需要SQL SERVER服务器中安装ORACLE客户端；

Exec sp_droplinkedsrvlogin demo,Null

Exec sp_dropserver demo

go

EXEC sp_addlinkedserver 

        @server ='demo', --要创建的链接服务器别名

        @srvproduct='Oracle', --产品名称     

 @provider='MSDAORA', --OLE DB 字符

 @datasrc='ServiceName'   --数据源oracle"ora92"network"admin"tnsnames.ora 查看

 

EXEC sp_addlinkedsrvlogin 

     'demo', --已建立的链接服务器名

     'false', -- 固定*/

     NULL, --为每个登陆SQL SERVER的用户使用此链接服务器，否则写用户名*/

     'userid', --帐号

     'password' –密码

go

 

SELECT * FROM demo.. USERID.TABLE

--注意用大写，因为在Oracle的数据字典中为大写

数据库的格式必须是链接服务器名..ORACLE用户名.表名.

 

SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )

UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101') 

SET name = 'hello';

INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')

VALUES ('hello');

DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');

如此则在SQL SERVER中就可以访问ORACLE的数据了。

建立的链接服务器可以在企业管理器中看见.

 

/////////////////////////////ACCESS////////////////////////////

--建立连接服务器

EXEC sp_addlinkedserver 

'ai',             --要创建的链接服务器名称 

‘access',     --产品名称    

'Microsoft.Jet.OLEDB.4.0', --OLE DB 字符

'd:"testdb"db.mdb' --数据源-- 盘符:"路径"文件名-- ""网络名"共享名"文件名 (网络版本) 

GO 

 

--创建链接服务器上远程登录之间的映射

--链接服务器默认设置为用登陆的上下文进行

--现在我们修改为连接链接服务器不需使用任何登录或密码

exec sp_addlinkedsrvlogin 'ai','false' 

go 

 

select * from ai...mytable 

 

/////////////////////////////EXCEL////////////////////////////

--建立连接服务器

EXEC sp_addlinkedserver 

'ai_ex',            --要创建的链接服务器名称 

'ex',     --产品名称         

'Microsoft.Jet.OLEDB.4.0', --OLE DB 字符

'd:"testdb"mybook.xls' , --数据源-- 盘符:"路径"文件名-- ""网络名"共享名"文件名 (网络版本) 

null, 

'Excel 5.0' --OLE DB 提供程序特定的连接字符串

GO 

 

----创建链接服务器上远程登录之间的映射

--链接服务器默认设置为用登陆的上下文进行

--现在我们修改为连接链接服务器不需使用任何登录或密码

exec sp_addlinkedsrvlogin 'ai_ex','false' 

go 

 

--查询数据

select * from ai_ex...sheet3$ 

 

/////////////////////////////MS SQL////////////////////////////

--建立连接服务器

EXEC sp_addlinkedserver 

'ai_mssql',        --要创建的链接服务器名称       

'ms',     --产品名称 

'SQLOLEDB', --OLE DB 字符

'218.204.111.111,3342' --数据源

GO 

 

--创建链接服务器上远程登录之间的映射

EXEC sp_addlinkedsrvlogin 

'ai_mssql', 

'false', 

NULL, 

'zhangzhe', --远程服务器的登陆用户名

'fish2231' --远程服务器的登陆密码

go 

 

--查询数据

select * from ai_mssql.pubs.dbo.jobs 

 

--还有一个更简单的办法

--这种方式在链接服务器建立后，它是默认开放RPC的

--建立连接服务器

EXEC sp_addlinkedserver 

'218.204.111.111,3342', --要创建的链接服务器名称

'SQL Server' --这里就用数据源作名称

GO 

 

--创建链接服务器上远程登录之间的映射

EXEC sp_addlinkedsrvlogin 

'218.204.111.111,3342', 

'false', 

NULL, 

'zhangzhe', --远程服务器的登陆用户名

'fish2231' --远程服务器的登陆密码

go 

 

--查询数据

select * from [218.204.253.131,3342].pubs.dbo.jobs 

go 

----------------------------------------------------------------------------------

if exists (select * from master.dbo.sysservers where srvname = 'srv_lnk')
    exec sp_dropserver 'srv_lnk','droplogins'
go

exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','192.168.2.238'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa',''

-- 格式
exec sp_addlinkedserver '链接名','','SQLOLEDB','服务器名'
exec sp_addlinkedsrvlogin '链接名','false',null,'用户名','密码' --用户名,密码空时为 Null

-- 使用方法 ,例:
select * from 链接服务器.数据库名.拥有者.表名