Create Linked Server

Creating a linked server called “RPT” that connect into SERVER01…

Note:

• When generating the linked server from a script from an existing database server, the rmtpassword parameter will not be set. After the linked server has been created, go to properties of the linked server and set the security context.

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'RPT', @srvproduct=N'x', @provider=N'SQLNCLI', @datasrc=N'SERVER01', @catalog=N'MyDB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RPT',@useself=N'False',@locallogin=NULL,@rmtuser=N'user01',@rmtpassword='mypw'
GO

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

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

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

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

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

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

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

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

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

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

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

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

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