更改SQL Server中链接服务器的属性

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.

是否有办法更改现有链接服务器的属性(产品名称、数据源、提供者字符串等)?当我进入属性屏幕时，所有选项都是灰色的。

8 个解决方案

#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.

在SQL Server management Studio中，点击链接服务器上的右键，选择“脚本链接服务器为'，然后选择'DROP and CREATE to'，然后选择“New Query Editor窗口”。您现在可以调整您想要在脚本中调整的任何设置，然后运行它。现有的链接服务器将被删除，并创建一个新的。

#2

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.:

惟一的选项是使用sp_setnetname。您可以使用它来更改链接服务器(目的地)的data_source，例如:

DECLARE @name sysname = 'SRVRNAME', @datasource sysname = 'srvr.name.com';
EXECUTE sp_setnetname @server = @name, @netname = @datasource;

#3

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.

将“SERVERNAME”替换为链接服务器的当前名称。将“NEWNAME”替换为要给链接服务器的新名称。

#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.

我可以使用sp_serveroption (@optname=N'name)更改链接服务器的名称。这个选项似乎不在sp_serveroption的BOL文档中。

#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.

查看sp_serveroption。无论如何，GUI最终都会这样做。如果您试图更改的内容最终不允许更改，那么您应该从这个存储过程中获得有意义的错误消息。

#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.

我的经验(我使用SQL Server 2016链接到SQL Server 2012实例，我想重命名链接服务器并更改它的目标)是我需要结合来自Xipooo和Jordan Parker的答案。

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

sp_serveroption重命名了链接服务器，sp_setnetname更改了链接服务器的目标。

#8

-2

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.

转到“开始管理工具”并打开数据源(odbc)，然后单击system dsn，在这里您将找到链接服务器dsn名称。从这里可以编辑链接服务器的属性。您还可以测试连接。

~ Kishore SG

~基肖尔SG

#1

#2