Friday, May 4, 2012

How to set sp_addlinkedserver connection


After executing this script you can run SQL queries across the servers. But make sure the user what you will mention here must be a past of those servers as well.


Just mention the server name with whom you want to set link and execute this script on that system from where you want to connect the link. This link is one side so if you wand bi-directional then need to execute the script on both the servers.

EXEC master.dbo.sp_addlinkedserver @server = N'SqlServername', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SqlServername',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Username',@rmtpassword='Password!'

GO

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'rpc', @optvalue=N'false'
GO

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

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

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

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

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

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

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

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