Changing Properties of a Linked Server in SQL Serv

问题:

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.

回答1:

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.

回答2:

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.

回答3:

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;

回答4:

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.

回答5:

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

回答6:

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.

回答7:

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.

回答8:

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