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.

Task

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'SAP Sybase IQ', @provider=N'SAOLEDB.16', 
     @datasrc=datasrc=N'Sybase IQ 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

Choose a new Linked Server name (SADATABASE is used in the example).
Choose an optional data source name (SAP Sybase IQ 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).

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