简单实用SQL脚本Part SQLServer 2005 链接服务器

SQL Server 2005版本的SSMS中已经有了 服务器对象->链接服务器 的功能点,用户首先创建一个远程DB的链接对象,之后就可以像本地表一样执行表的DML了。

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

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

创建步骤:在SQL Server 2005版本打开SSMS,服务器对象->链接服务器->右击 新建链接服务器,在图2中是一种设置方式,也有其它的设置方式,比如:[图解]sqlserver中创建链接服务器,图3是安全性选项中设置远程数据库的账号和密码。

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图1:新建链接)

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图2:设置链接)

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图3:设置帐号)

注意事项: 在MSSQL2005中Rpc的默认设置如图4所示, 需要把它设置为图5, 右键点击远程链接->属性->服务器选项->Rpc和Rpc Out,这两个值需要设置为True。

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图4: 默认设置)

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图5: 正确设置)

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

USE[master]
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’rpc’,@optvalue=N’true’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’rpcout’,@optvalue=N’true’
GO

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

简单实用SQL脚本Part SQLServer 2005 链接服务器

(图6: 生成SQL脚本)

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

/******对象:LinkedServer[ETV2_LINK]脚本日期:09/08/201017:36:11******/
EXECmaster.dbo.sp_addlinkedserver@server=N’ETV2_LINK’,@srvproduct=N’ETV2_LINK’,@provider=N’SQLNCLI’,@datasrc=N’BWA035\\BWA035_2K5′
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’collationcompatible’,@optvalue=N’false’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’dataaccess’,@optvalue=N’true’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’dist’,@optvalue=N’false’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’pub’,@optvalue=N’false’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’rpc’,@optvalue=N’true’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’rpcout’,@optvalue=N’true’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’sub’,@optvalue=N’false’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’connecttimeout’,@optvalue=N’0′
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’collationname’,@optvalue=null
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’lazyschemavalidation’,@optvalue=N’false’
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’querytimeout’,@optvalue=N’0′
GO
EXECmaster.dbo.sp_serveroption@server=N’ETV2_LINK’,@optname=N’useremotecollation’,@optvalue=N’true’

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

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

–查询远程DB表TableName
select*fromETV2_LINK.[etV2_Online].dbo.VisiteLog_20100629

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

–注意:是sys.objects不是sysobjects
–判断远程用户是否存在某张表
IFEXISTS(SELECT*FROMETV2_LINK.[etV2_Online].sys.objectsWHEREname=N’VisiteLog_20100629′ANDtypein(N’U’))
BEGIN
–逻辑处理
print‘存在表’
END

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

–判断远程用户是否存在某张表(参数化表名),返回变量
DECLARE@IsExistTableVARCHAR(10)
DECLARE@TablenameVARCHAR(50)
DECLARE@sqlStringNVARCHAR(4000)
SET@IsExistTable=‘False’
SET@Tablename=‘VisiteLog_’+convert(varchar(9),getdate()-1,112)–例如VisiteLog_20100629
SET@sqlString=
‘IFEXISTS(SELECT*FROMETV2_LINK.[etV2_Online].sys.objectsWHEREname=N”’+@Tablename+”’ANDtypein(N”U”))
set@IsExistTableOUT=”True”’
EXECsp_executesql@sqlString,N’@IsExistTableOUTvarchar(10)OUTPUT’,@IsExistTableOUT=@IsExistTableOUTPUT
IF(@IsExistTable=‘True’)–存在
BEGIN
–逻辑处理
print‘存在表’
END

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

–方法1:
select*fromopenrowset(‘SQLOLEDB’,’server=192.168.0.67;uid=sa;pwd=password’,’SELECT*FROMBCM2.dbo.tbAppl’)
–方法2:
select*fromopenrowset(‘SQLOLEDB’,’192.168.0.67′;’sa’;’password’,’SELECT*FROMBCM2.dbo.tbAppl’)
作者:听风吹雨

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/22250/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-16 1:16:30
下一篇 2022-09-16 1:16:35

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!