SP_addlinkedserver

Question

SP_addlinkedserver

J-983369

Mr or Mrs. 500

Points: 535
More actions
September 23, 2009 at 3:02 am

#214689

Hi,
I have a linked setup where the name has changed. When I do a select * from sys.servers, I can see the name of the server but the OS name has been renamed and I need to be able to rename this link? Is there anyway I can do this? I have tried to create a new link but I am not able to replicate the same settings as the original?
Any assistance would be much appreciated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

J-983369 (9/23/2009)
Hi,
I have a linked setup where the name has changed. When I do a select * from sys.servers, I can see the name of the server but the OS name has been renamed and I need to be able to rename this link? Is there anyway I can do this? I have tried to create a new link but I am not able to replicate the same settings as the original?
Any assistance would be much appreciated.
Thanks

I take it that the OS server name has changed, just create a new linked server. Not sure what the problem is?

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

Try to get help of sp_addserver and sp_dropserver from BOL.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 3

Hi,

Yes the server name has changed. I have created a new link but the issue I have is some of the setting e.g. is_linked is 1 and is_remote_login_enabled is 0. How can I update this?

Rather than recreating a new link is there anyway I can just change the name on the original link?

Silverfox SSC-Insane Points: 23150 More actions · Answer 4

J-983369 (9/23/2009)
Hi,
Yes the server name has changed. I have created a new link but the issue I have is some of the setting e.g. is_linked is 1 and is_remote_login_enabled is 0. How can I update this?
Rather than recreating a new link is there anyway I can just change the name on the original link?

No, in management studio, go to server objects, then linked servers, right click on the existing linked server and then click on script linked server as, then create to, then new query window. in the generated script, change the server name to what you want it to be.

if you want to change the individual settings but not the server name, you can do this for example.

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'use remote collation', @optvalue=N'true'

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 5

Hi,

Thanks again. I have managed to recreate a new link and restarted sql server but when I run select@@servername I get NULL in column name?

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 6

Hi,

Thanks for your all, I've managed to resolve it as I didn't add local.

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

J-983369 (9/23/2009)
Hi,
Thanks again. I have managed to recreate a new link and restarted sql server but when I run select@@servername I get NULL in column name?

Glad to hear it, so it all works now I take it.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 8

Well, I'm waiting for the guy to test via his application as he was getting rollback transaction errors! I may need to come back perhaps but when I do a select@@servername I'm getting the right servername now