Post reply

How to update catelog view sys.servers

  • January 20, 2012 at 5:43 am

    #249525

    Hi

    I have some 200-300 linked servers created on my database server.Server Instance of databases to which this linked servers are pointed now changed.How can i update changed server instance in my sys.servers catalog view?? Do i have to drop and recreate all this linked servers manually or can have some T-SQL script to do it in one shot?

    Thanks

  • January 20, 2012 at 5:53 am

    #1436409

    I think you have to drop and recreate the linked servers again.

  • January 20, 2012 at 5:57 am

    #1436413

    Is there a way to do that with the help of a T-SQL script or i have to manually do it one by one?

  • January 20, 2012 at 6:42 am

    #1436445

    It'd be simple enough to use the results of

    sp_linkedservers

    to return a list of all linked servers, and then loop through each server dynamically to update a specific property.

    _____________________________________________________________________
    Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

  • January 20, 2012 at 6:56 am

    #1436457

    Hi,

    You can script all the linked servers at once from SSMS -> Object Explorer Details (Press F7) ->Server Objects ->Linked Servers and select all the linked servers ,right click and select Script Linked Server as-> Drop and create to -> New query window.

    There you can make changes that you required for all at once. Please find attachment.

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

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