Setting up a Linked Server using a script

A Linked Server definition may be set up using a SQL Server script.

Prerequisites

SQL Server 2005 or later.

Context and remarks

Make the appropriate changes to the following script using the steps below before running it on SQL Server.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server=N'SADATABASE', 
     @srvproduct=N'SQL Anywhere', @provider=N'SAOLEDB.16', 
     @datasrc=N'SQL Anywhere 16 Demo',
     @provstr=N'host=localhost:4444;server=myserver;dbn=demo'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc out', @optvalue=N'true'
GO
-- Set remote login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SADATABASE', 
     @locallogin = NULL , @useself = N'False', 
     @rmtuser = N'DBA', @rmtpassword = N'sql'
GO
-- Set global provider "allow in process" flag
EXEC master.dbo.sp_MSset_oledb_prop N'SAOLEDB.16', N'AllowInProcess', 1

Task

Choose a new Linked Server name (SADATABASE is used in the example).
Choose an optional data source name (SQL Anywhere 16 Demo is used in the example).
Choose an optional provider string (N'host=localhost:4444;server=myserver;dbn=demo' is used in the example).
Choose a remote user ID and password (N'DBA' and N'sql' are used in the example).

Results

Your modified script can be run under Microsoft SQL Server to create a new Linked Server.

Discuss this page in DocCommentXchange.