Introduction

Two of the easiest ways to modify linked server properties in SQL Server are to use provided templates from the Template Browser and use the DROP/CREATE TO scripting function from an existing linked server. We can use these scripts to change the linked server alias, data source, and a wide range of other options. 

In this article, we are going to walk through changing a linked server alias and data source using three server examples and then take a look at other linked server properties. Our sample servers are SQLFundO, SQLFundA, and SQLFundI.  We already have a linked server on SQLFundO using SQLFundI as a data source.

Accessing the Template Explorer and Linked Server Stored Procedures

We can access the Template Browser by using CTRL+ALT+T in SSMS or choosing Template Explorer under View in the ribbon.

Figure 2-1  Template Browser accessed by using CTRL+ALT+T.

There is no ALTER option becuase we have to drop the linked server to make modifications. The code below is generated from scripting a Drop Linked Server and Add Linked Server template.

-- ==============================
-- Drop Linked Server template
-- ==============================
-- Drops a linked server reference to a Database Engine instance
-- Related logins will also be dropped in this example
EXEC master.dbo.sp_dropserver
@server=N'<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>'
, @droplogins='droplogins'
GO
-- =====================================
-- Add Linked Server Simplet template
-- =====================================
-- Create linked server reference to Database Engine instance on same machine
EXEC master.dbo.sp_addlinkedserver
@server = N'<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>'
, @srvproduct=N'SQL Server'
GO
-- Use current login's security context for the link
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>'
, @locallogin = NULL
, @useself = N'True'
GO
-- Run a test query
SELECT * 
FROM [<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>].<database_name,sysname,pubs>.<schema_name,sysname,dbo>.<table_name,sysname,authors>
GO

For the moment, we are going to ignore the test query, login context, and all the commented code.  We are now ready to start changing some properties of our linked server.

EXEC master.dbo.sp_dropserver
@server=N'<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>'
, @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'<server_name,sysname,(local)><instance_name,sysname,\SQLEXPRESS>'
, @srvproduct=N'SQL Server'
GO

Changing Linked Server Name

In the introduction, I said I already have a linked server on SQLFundo pointing to SQLFundI.  I am going to fill in the variables of my linked server using the script above and also add two more variables: @provider and @provstr.

EXEC master.dbo.sp_dropserver
  @server=N'SQLFundI_LinkedServ'
, @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver 
  @server = N'SQLFundI_LinkedServ'
, @srvproduct=N'SQL_Server'
, @provider=N'SQLNCLI'
, @provstr=N'PROVIDER=SQLOLEDB;SERVER=SQLFUNDI'

This linked server is called SQLFundi_LinkedServ (@Server variable) and it is using the SQLFundi server as the data source (Server=SQLFundi in the @provstr variable).  Before we add this linked server, I can shorten the name so I do not have to write out the whole SQLFundi_LinkedServ alias in my queries.  

EXEC master.dbo.sp_dropserver
  @server=N'SQLFundI_LinkedServ'
, @droplogins='droplogins'
GO

EXEC master.dbo.sp_addlinkedserver 
  @server = N'FundI'
, @srvproduct=N'SQL_Server'
, @provider=N'SQLNCLI'
, @provstr=N'PROVIDER=SQLOLEDB;SERVER=SQLFUNDI'

The new linked server alias is Fundi and it has the same data source as before. Now I can test the connection and view results.

SELECT *
 FROM FundI.Employees.dbo.Users

Figure 3-1 Querying the employee database on SQLFundI shows one record.  

Changing Linked Server Data Source

Let’s suppose I want to leave the linked server alias the same, but now I want to point it to SQLFundA instead.  I am going to use the same scripts from before but modify the @provstr variable this time and change it to use SQLFundA. 

EXEC master.dbo.sp_dropserver
  @server=N'FundI'
, @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
  @server = N'FundI'
, @srvproduct=N'SQL_Server'
, @provider=N'SQLNCLI'
, @provstr=N'PROVIDER=SQLOLEDB;SERVER=SQLFUNDA'

I can run my same query against the employees database, but this time I will get data returned from the SQLFundA server instance. 

SELECT *
FROM FundI.Employees.dbo.Users

Figure 4-1 Using the same query, we see different results becuase the data on SQLFundA is different than SQLFundI.

This is extremely convenient when I want to query data from another server and not have to change my queries to a new server name.  

Changing general linked server properties

We can change other properties (as well as the data source and alias) by using the DROP and CREATE TO script provided through the Object Explorer with our existing linked server.  

Figure 5-1  Scripting a linked server

Scripting a linked server using the Object Explorer (F8) or by clicking Object Explorer under View in the ribbon. Again, notice the absence of an ALTER script. The script below is what gets generated from my existing linked server:

/****** Object:  LinkedServer [Fundi]    Script Date: 6/1/2016 7:03:29 AM ******/EXEC master.dbo.sp_dropserver
@server=N'Fundi'
, @droplogins='droplogins'
GO
/****** Object:  LinkedServer [Fundi]    Script Date: 6/1/2016 7:03:29 AM ******/EXEC master.dbo.sp_addlinkedserver
@server = N'Fundi'
, @srvproduct=N'SQL_Server'
, @provider=N'SQLNCLI'
, @provstr=N'PROVIDER=SQLOLEDB;SERVER=SQLFunda'
 /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Fundi'
, @useself=N'True'
, @locallogin=NULL
, @rmtuser=NULL
, @rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Fundi', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

This DROP/CREATE TO script provided a long list of options that can be configured when creating a linked server.  This list matches for the most part what is provided when you select to create a new linked server through the Object Explorer. 

Figure 5-2 Adding linked server using Object Explorer

The General page of the New Linked Server prompt provides fields that are very similar to the code used at the beginning of the article from the Add Linked Server Simple procedure.  Just like the built-in system procedure , we can specify name, data source, provider string, and even choose a catalog as well.  

Figure 5-3 General page of New Linked Server interface

The Server Options tab gives options very similar to the scripting method used in Figure 5-1.  This tab is where more granular options can be specified for the Linked Server (A summary of each option is provided in a link at the end of this article). 

Figure 5-4 Server Options page of New Linked Server interface

For a summary of the linked server properties available for configuration, see the Microsoft documentation pages

• General Properties 
• Security Properties
• Server Options