Sample Linked Server Script

A sample SQL script for adding Navigate as a linked server, allowing your college MIS to connect to the Navigate external feeds database.

Replace USERNAME_HERE and PASSWORD_HERE with your own connection credentials before running the script.

Add the linked server

EXEC master.sys.sp_addlinkedserver @server = N'Navigate' ,
                                   @srvproduct = N'' ,
                                   @provider = N'SQLNCLI' ,
                                   @datasrc = N'navigatedbserver.database.windows.net,1433' ,
                                   @catalog = N'Navigate_ExternalFeeds';

Add the linked server login

EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname = N'Navigate' ,
                                     @useself = N'False' ,
                                     @locallogin = NULL ,
                                     @rmtuser = N'USERNAME_HERE' ,
                                     @rmtpassword = 'PASSWORD_HERE';
GO

Set the server options

EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'collation compatible' , @optvalue = N'false';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'data access' , @optvalue = N'true';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'dist' , @optvalue = N'false';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'pub' , @optvalue = N'false';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'rpc' , @optvalue = N'true';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'rpc out' , @optvalue = N'true';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'sub' , @optvalue = N'false';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'connect timeout' , @optvalue = N'0';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'collation name' , @optvalue = NULL;
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'lazy schema validation' , @optvalue = N'false';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'query timeout' , @optvalue = N'0';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'use remote collation' , @optvalue = N'true';
GO
EXEC master.sys.sp_serveroption @server = N'Navigate' , @optname = N'remote proc transaction promotion' , @optvalue = N'true';
GO

Source document attached below.