Jasinski Technical Wiki

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

PoweredBy

Creating a Linked Server with an Alias - SQL Server

RSS
Modified on Sat, Jan 17, 2009, 9:17 AM by Administrator Categorized as 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'

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.