SQL Server 创建链接服务器
2010-09-09 14:43 by 听风吹雨, 33588 阅读, 7 评论, 收藏, 编辑适用场景:对远程的DB进行操作。
2000与2005对比:在SQL Server 2000版本中也有链接远程DB的SQL,但是功能比较弱,扩展性差,支持的查询比较简单。而SQL Server 2005版本的SSMS中已经有了 服务器对象->链接服务器 的功能点,用户首先创建一个远程DB的链接对象,之后就可以像本地表一样执行表的DML了。
创建步骤:在SQL Server 2005版本打开SSMS,服务器对象->链接服务器->右击 新建链接服务器,在图2中是一种设置方式,也有其它的设置方式,比如:[图解]sqlserver中创建链接服务器,图3是安全性选项中设置远程数据库的账号和密码。
(图1:新建链接)
(图2:设置链接)
(图3:设置帐号)
注意事项: 在MSSQL2005中Rpc的默认设置如图4所示, 需要把它设置为图5, 右键点击远程链接->属性->服务器选项->Rpc和Rpc Out,这两个值需要设置为True。
(图4: 默认设置)
(图5: 正确设置)
但在MSSQL2008下不能直接修改链接服务器 'ETV2_LINK' 的RPC配置成TURE,可以通过语句修改如下:
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远程链接的数据库对象。
(图6: 生成SQL脚本)
SQL Server 2005生成远程链接对象的SQL脚本:
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 [链接服务器名].[远程数据库名].[所有者].[表名]
select * from ETV2_LINK. [ etV2_Online ].dbo.VisiteLog_20100629
使用场景2: 判断ETV2_LINK这个远程链接对象的[etV2_Online]数据库中是否存在名为VisiteLog_20100629的表。
-- 判断远程用户是否存在某张表
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)
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 ')