Can SQL Server 2008 Linked Servers be edited? Is there the equivalent of an ALTER statement for Linked Servers?

Question

I have a Linked Server which was made using SQL Server Management Studio 2008.

If the linked server destination changes, I will have to change the properties of it to direct to the right place.

But I cannot - there is no edit option!

Am i missing something, or can Linked Servers not be altered?

score 2 · Answer 1 · answered Jul 28 '10 at 12:35

2

Yes, you are right. Linked server name cannot be modified. It needs to be dropped and recreated in case the server name changes.

answered Jul 28 '10 at 12:35

Nomad

score 1 · Answer 2 · answered Jan 17 '13 at 15:05

You can't modify a Linked Server

But if you right click the server in Management Studio and then:

Script Linked Server As > DROP and CREATE to > New Query Editor Window

You can make changes to the script and it's almost the same workflow as using ALTER

score 0 · Answer 3 · answered Jul 28 '10 at 19:05

0

The ability to edit them left around SQL 2000 or SQL 2005, now they have to be dropped and re-created.

answered Jul 28 '10 at 19:05

mrdenny

score 0 · Answer 4 · answered Jul 28 '14 at 22:33

Check out sp_setnetname. You can use it to change the data_source of the linked server (destination), e.g.:

DECLARE @name sysname = 'SRVRNAME';
DECLARE @datasource sysname = 'srvr.name.com';

EXECute sp_setnetname @server = @name, @netname = @datasource;