MSSQLSERVER cross-server connection server creation method:
Copy codeThe Code is as follows:
-- Declare Variables
Declare @ svrname varchar (255 ),
@ Dbname varchar (255 ),
@ Sapassword varchar (255)
-- Modify the following variables before execution.
Select @ svrname = 'target server name ',
@ Dbname = 'target database ',
@ Sapassword = target Database Password
If exists (select srvname from master. dbo. sysservers where srvname = 'linked server name ')
Exec sp_dropserver @ server = 'linked server name', @ droplogins = 'droplogins'
-- Add a linked server
Exec sp_addmediaserver @ server = 'linked server name', @ srvproduct = '', @ provider = 'sqloledb', @ datasrc = @ svrname, @ catalog = @ dbname
-- Added remote logon to the linked server.
Exec sp_addmediasrvlogin @ rmtsrvname = 'linked server name', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa', @ rmtpassword = @ sapassword
-- Configure Parameters
Exec sp_serveroption @ server = 'linked server name', @ optname = 'use remote collation ', @ optvalue = 'false'
Exec sp_serveroption @ server = 'linked server name', @ optname = 'collation compatible ', @ optvalue = 'true'
Go