[Sqlserver] stored procedures for linking databases

Create a database link: /**/ /*--------------------

-- Running example: pro_linkserver 'dbserver', '192. 168.1.1 ', 'sa', 'sapassword'
-- Operation database after running: Select * from DB. Pubs. DBO. Titles
-- Parameter:
-- @ Linknm: link alias
-- @ Rmserv: link address
-- @ Rmuser: User Name
-- @ Rmpawd: User Password

---------------------*/

Create   Procedure Hd_addlinkserver
@ Linknm Sysname,
@ Rmserv Sysname,
@ Rmuser Sysname,
@ Rmpawd Sysname,
@ Flag   Tinyint = 0   -- 0: Add 1: Drop 2: List
As
Begin
If   @ Flag ! = 2   And   Isnull ( @ Linknm , '' ) = ''
Begin
Raiserror ( ' The linked server name cannot be blank. ' , 16 , - 1 )
Return
End

If   @ Flag = 0   And   Isnull ( @ Rmuser , '' ) = ''
Begin
Raiserror ( ' The remote login user name cannot be blank. ' , 16 , - 1 )
Return
End

Declare   @ Err   Int

If   @ Flag = 0
Begin
If   Isnull ( @ Rmserv , '' ) = ''   Select   @ Rmserv = ' 127.0.0.1 '
Exec   @ Err = Master. DBO. sp_addmediaserver @ Linknm

If   @ Err = 0
Begin
Exec   @ Err = Master. DBO. sp_serveroption @ Linknm , ' Use Remote collation ' , ' False '
If   Isnull ( @ Rmserv , '' ) Not   In ( '' , @ Linknm )
Exec   @ Err = Master. DBO. sp_setnetname @ Linknm , @ Rmserv
If   @ Err = 0
Exec   @ Err = Master. DBO. sp_add1_srvlogin @ Linknm , ' False ' , Null , @ Rmuser , @ Rmpawd
End
End   Else
If   @ Flag = 1
Begin
Exec   @ Err = Master. DBO. sp_dropserver @ Linknm , ' Droplogins '
End   Else
If   @ Flag = 2
Begin
Exec   @ Err = Master. DBO. sp_helpserver
Select   *   From Master. DBO. sysservers
End

If   @ Err = 0   Print   ' SQL Server link success '   Else   Print   ' SQL Server link fail '

End
Go

Delete link: /**/ /*******************************
-- Function:
-- Create sqlserver database link ing
-- Parameter description:
-- @ Sqlserver: Server IP Address/Server alias
********************************/
Create   Proc Hd_droplinkserver
@ Sqlserver   Nvarchar ( 50 )
As
Exec Sp_dropserver @ Sqlserver , ' Droplogins '

Go

application example after link creation: select * from [ dbserver ] . [ pubs ] . [ DBO ] . [ titles ]