How to update a linked server connection string?

Question

I'm using below statement for addling a linked server:

EXEC sp_addlinkedserver
@server = 'north',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=192.168.100.147;UID=sa;PWD=xxxx;'

Now I want to update the linked server connection string, for example the server name and etc. Is there any statement to do it?

My remote database is using for replication, isn't there any problem if I do it? — Raymond Morphy
– Raymond Morphy, Commented May 1, 2011 at 4:55
My remote database uses "transactional replication" and it is as "publisher" an my database wants to connect to it with linked server — Raymond Morphy
– Raymond Morphy, Commented May 1, 2011 at 5:05
@Raymond Morphy, so, which of the servers has the linked server defined? Your database? if so, then it's safe to drop and recreate the linked server. — dawebber
– dawebber, Commented May 1, 2011 at 5:39

gbn · Accepted Answer · 2011-05-01 08:11:54Z

3

To change server name of the linked server use sp_setnetname. Changing other things doesn't make sense (eg provider) which is why there is no alter proc.

In this case it won't work because you haven't followed the sp_addlinkedserver example. In fact, I can't see why you'd set up a linked server to SQL Server this way. You'd use sp_addlinkedsrvlogin for credentials too

EXEC sp_addlinkedserver
  @server = 'north',
  @srvproduct = 'SQL Server'
GO
EXEC sp_setnetname 'north', '192.168.100.147'
GO
EXEC sp_addlinkedsrvlogin 'north', NULL, NULL, 'sa', 'XXXX'
GO

If you don't want to do it this way, then drop and recreate it however you want. That is, you have to drop and recreate it because you have set this up in a non-standard fashion

answered May 1, 2011 at 8:11

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update a linked server connection string?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related