23

Is there any way to change the properties (Product name, data source, provider string, etc...) of an existing linked server? When I go to the properties screen, all the options are grayed out.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248

9 Answers9

35

Here's the command.

EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'name', @optvalue=N'<NEWNAME>'

Replace 'SERVERNAME' with the current name of the linked server. Replace 'NEWNAME' with the new name you want to give the linked server.

Xipooo
  • 1,496
  • 1
  • 14
  • 13
  • I tried this solution and it works (on SQL 2016 SP1). – Magier Jan 06 '17 at 13:37
  • 2
    This works for the name of the Linked Server, and for a handful of other options, but unfortunately not the data source. Details on supported options are here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-serveroption-transact-sql?view=sql-server-2017 – Mike Feb 18 '19 at 03:06
  • Works wonderfully, thanks. After renaming my linked server, I right clicked it, SCRIPT AS DROP AND CREATE, and as far as I can tell, it no longer creates a reference to the actual location of the linked server. This looks a little problematic, isn't it? – Ethan1701 Sep 10 '20 at 14:21
27

In SQL Server management Studio click right on the linked server, choose "Script Linked Server as' then choose 'DROP and CREATE to' and then "New Query Editor Window'. You can now adjust any settings that you want to adjust in the script and then run it. The existing linked server will be dropped and a new one created.

Jaap Kramer
  • 271
  • 3
  • 3
8

The only option you have is to use sp_setnetname. You can use it to change the data_source of the linked server (destination), e.g.:

DECLARE @name sysname = 'SRVRNAME', @datasource sysname = 'srvr.name.com';
EXECUTE sp_setnetname @server = @name, @netname = @datasource;
Jordan Parker
  • 1,208
  • 1
  • 16
  • 25
  • 4
    FYI. This only works for linked SQL Servers. If the linked server is not a SQL Server, it will not work. – Abraham Apr 30 '15 at 22:04
  • And sometimes not even then - just set up a linked server on a R2 box to a 2005 box, then tried using sp_setnetname to point at a 2012 SQL Server. Says it succeeded, but still queried the old box and when I rescripted out the linked server still showed as going to the original.. – mbourgon Feb 22 '16 at 21:05
3

I was able to change the name of a linked server using sp_serveroption with the @optname=N'name'. This option does not appear to be in the BOL documentation on sp_serveroption.

E Lyons
  • 31
  • 3
2

I ended up creating a new linked server and deleting the old one. Unfortunately, there is no way to edit an existing instance

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
  • 2
    Not exactly. It depends, of course, which property you want to change. However, following my answer you will find that you can change some of the options using a single script instruction – Steve Aug 08 '12 at 14:23
1

Merge of various responses, as well as reading documentation - this is only documented to work for SQL linked servers, not alternate data sources:

select server_id, name, data_source from sys.servers where product = 'SQL Server'

DECLARE @oldName nvarchar(30) = 'oldSERVER', --must match current entry under sys.servers.name
@name sysname = 'newServer', 
@datasource sysname = 'newServer.DNSDomainName.com' -- can be a windows FDQN that is not SQL valid if needed for RPC cross domain resolution

/* Comment out this marker to perform update
EXEC master.dbo.sp_serveroption @server=@oldName, @optname=N'name', @optvalue=@name
EXECUTE sp_setnetname @server = @name, @netname = @datasource;

select server_id, name, data_source from sys.servers where product = 'SQL Server'
--*/
Tla2c
  • 21
  • 1
  • If I run the first select from the sys.servers it does not show me the linked servers. It shows me only the current local SQL server, although the linked server is also a SQL Server 2016. If you remove the "where product = 'SQL Server" it shows me the linked servers as well. – Vlad S. Jul 05 '21 at 15:02
  • Actually better use "select server_id, name, data_source from sys.servers where is_linked=1" to see only the linked servers. – Vlad S. Jul 05 '21 at 15:15
0

Check out sp_serveroption. This is how the GUI would ultimately do it anyways. If changing what you were trying to change is ultimately not allowed, you should get a meaningful error message from this stored procedure.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
0

My experience (I'm using SQL Server 2016 to link to a SQL Server 2012 instance, and I wanted to rename the linked server and change it's target) was that I needed to combine the answers from Xipooo and Jordan Parker.

sp_serveroption renamed the linked server, and sp_setnetname changed the target of the linked server.

paulH
  • 1,102
  • 16
  • 43
-3

Go to start-administrative tools and open the data sources(odbc) then click on system dsn, here you will find the linked server dsn name. From here you can edit the properties of linked server. You can also test the connection.

~ Kishore SG

Community
  • 1
  • 1
user3230869
  • 1