Linked server

Question

Linked server

Janda Nel

SSC Eights!

Points: 882
More actions
August 19, 2015 at 4:30 am

#310995

good day .
we have a development environment with linked servers,which worked well.We had to rename the server names for reasons.Now in the one server the linked server appear in the sys.servers table,but do not appear in the linked servers option under server objetcs.I tried to re-add the server,but get message that it exists already.I tried to delete it from the sys.servers table,but get the message that ad hoc updates to system catalogs are not allowed.
Any suggestions how to fix this?I do need the linked server,because I export data from the one server to the other one.
Both of the servers are sql 2012.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Janda Nel (8/19/2015)
good day .
we have a development environment with linked servers,which worked well.We had to rename the server names for reasons.Now in the one server the linked server appear in the sys.servers table,but do not appear in the linked servers option under server objetcs.I tried to re-add the server,but get message that it exists already.I tried to delete it from the sys.servers table,but get the message that ad hoc updates to system catalogs are not allowed.
Any suggestions how to fix this?I do need the linked server,because I export data from the one server to the other one.
Both of the servers are sql 2012.

you could have used the SSMS GUI to drop the existing linked server.

From Code you have to call a specific stored procedure to drop the existing linked server. then you can add it back.

--===== Drop the text server

EXEC dbo.sp_DropServer 'MyLinkedServer', 'DropLogins' --used to point to -[PROD-SQL-2008R2]

--#################################################################################################

--Linked server Syntax for SQL Server With Alias

--#################################################################################################

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'PROD-SQL-2014', @provider = N'SQLOLEDB';

-- Add Default Login (if applicable)

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'False',

@locallogin = NULL,

@rmtuser = N'SomeSQLuserOnLinkedServer',

@rmtpassword = 'NotARealPassword';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Janda Nel SSC Eights! Points: 882 More actions · Answer 2

thank you,I will go and try that now and let you know if it worked.

Janda Nel SSC Eights! Points: 882 More actions · Answer 3

Janda Nel

SSC Eights!

Points: 882

August 19, 2015 at 7:15 am

#1821032

thanks it worked,I can see the linked server now