Creating Linked Server through Query in SQL Server

Creating Linked Server through Query in SQL Server

-- DROPPING IF EXISTS
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
                                                                                      AND srv.name =@ServerName)
BEGIN
            EXEC master.dbo.sp_dropserver @server=@ServerName, @droplogins='droplogins'
END

-- CREATING LINKED SERVERS
EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=N'SQL Server'

--The linked server remote logins
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@ServerName,@useself=N'False',@locallogin=NULL,@rmtuser=@UserID,@rmtpassword=@Pwd
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'collation compatible',           
                                                                                                                    @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'data access', 
                                                                                                                  @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'dist', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'pub', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc', @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc out', @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'sub', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'connect timeout',       
                                                                                                @optvalue=N'0'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'collation name',
                                                                                                @optvalue=null
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'lazy schema validation',
                                                                                                @optvalue=N'false'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'query timeout',
                                                                                               @optvalue=N'0'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'use remote collation', 
                                                                                               @optvalue=N'true'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName,
                                                      @optname=N'remote proc transaction promotion', @optvalue=N'true'
--GO