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.
-
In SSMS if you "connect" (upper left) you can manage the server if you have authority.paparazzo– paparazzo2012-08-08 14:05:29 +00:00Commented Aug 8, 2012 at 14:05
Add a comment
|
9 Answers
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 Comments
Magier
I tried this solution and it works (on SQL 2016 SP1).
Mike
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: learn.microsoft.com/en-us/sql/relational-databases/…
Ethan1701
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?
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.
1 Comment
Monty Wild
This doesn't recreate any passwords for credentials (unless they are '#######")
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;
2 Comments
ElRojo
FYI. This only works for linked SQL Servers. If the linked server is not a SQL Server, it will not work.
mbourgon
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..
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.
Comments
I ended up creating a new linked server and deleting the old one. Unfortunately, there is no way to edit an existing instance
1 Comment
Steve
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
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'
--*/
2 Comments
Vlad S.
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.
Actually better use "select server_id, name, data_source from sys.servers where is_linked=1" to see only the linked servers.
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.
Comments
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.
Comments
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
