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?

Solution

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

Comments

[OMG Ponies]

Just as easy to drop & recreate the object...