mssqlserver跨服务器链接服务器创建方法如下
复制代码 代码如下:
–声明变量
declare @svrname varchar(255),
@dbname varchar(255),
@sapassword varchar(255)
–请在执行前，根据实际情况修改下列变量
select @svrname = ‘目标服务器名’,
@dbname = ‘目标数据库’,
@sapassword = 目标数据库密码
if exists ( select srvname from master.dbo.sysservers where srvname =’链接服务器名’)
exec sp_dropserver @server = ‘链接服务器名’,@droplogins =’droplogins’
–增加链接服务器
exec sp_addlinkedserver @server = ‘链接服务器名’,@srvproduct=”,@provider = ‘sqloledb’, @datasrc =@svrname, @catalog =@dbname
–增加链接服务器远程登录
exec sp_addlinkedsrvlogin @rmtsrvname = ‘链接服务器名’,@useself =’false’,@locallogin = ‘sa’,@rmtuser =’sa’,@rmtpassword =@sapassword
–配置参数
exec sp_serveroption @server =’链接服务器名’,@optname = ‘use remote collation’,@optvalue =’false’
exec sp_serveroption @server =’链接服务器名’,@optname = ‘collation compatible’,@optvalue =’true’
go