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.
9 Answers
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'
--*/
Related