USE MASTER
GO

IF  EXISTS (SELECT name FROM sys.servers WHERE name ='SDG00DB01')
   EXEC master.dbo.sp_dropserver @server='LinkServerName', @droplogins='droplogins' 
GO   

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @srvproduct=N'SQL Server' ; 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkServerName',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxxxx',@rmtpassword=N'xxxxx'

GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'use remote collation', @optvalue=N'true'
GO

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-2017

Last modified: April 26, 2019

Author

Comments

Write a Reply or Comment