MS SQL Server

Link Server Creation using the Preferred Name

Link Server

Objective:

Link Server creation in any preferred name like MyTelecomLink, can be done via the below script.

Here @server is the preferred name in the below script

And @datasrc is the SQL Server Instance Name along with port number if any.

The below script is tested on SQL Server 2016.

USE [master]
GO

/****** Object:  LinkedServer [MYTELECOMLINK]    Script Date: 8/3/2017 11:03:31 AM ******/
-- The below line is commented out. If you want to drop the linked server which was already created using your preferred name, then you can drop that.
--EXEC master.dbo.sp_dropserver @server=N'MYTELECOMLINK', @droplogins='droplogins'
--GO


EXEC master.dbo.sp_addlinkedserver @server = N'MYTELECOMLINK', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'SQLSERVERANDINSTANCENAME,1443'

GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTELECOMLINK', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYTELECOMLINK', @locallogin = NULL , @useself = N'False', @rmtuser = N'mobileworking_link', @rmtpassword = N'M0b1l3working#'
GO

 

Leave a Reply

Your email address will not be published. Required fields are marked *