Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Creating a Linked Server with an Alias - SQL Server

In SQL Server, to create a linked server with an alias (i.e., a name other than the host name), execute the following procedure, specifying your desired values for the @Alias and @HostName variables.

DECLARE 
     @Alias varchar(max)
    ,@HostName varchar(max)

select
     @Alias = 'MyAlias'
    ,@HostName = 'MyServerName\MyInstanceName'

USE [master]

EXEC master.dbo.sp_addlinkedserver @server = @Alias, @srvproduct=N'SQL_SERVER', 
    @provider=N'SQLNCLI', @datasrc=@HostName
    
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'collation compatible', 
    @optvalue=N'false'
    
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'lazy schema validation', 
    @optvalue=N'false'
    
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@Alias, @optname=N'remote proc transaction promotion', 
    @optvalue=N'true'
    
USE [master]
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @Alias, @locallogin = NULL , @useself = N'True'