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