HEELPBOOK - SQL Server - Delete All Linked Servers on single server ######################


############## SCENARIO

I'm using SQL Server Management Studio 2008 (ssms.exe) connected with a local SQL Server 2000, so I notice that every time I try enter on Linked Server option It crash inmediatly so I want to delete all the servers linkeds there for try again.

What script should I use or what command on T-SQL I have to run for delete all and without specifying the name of each server linked.


############### SOLUTION

You can execute sp_dropserver for all linked servers using the database cursor. The following example shows how to do this.



DECLARE @sql NVARCHAR(MAX)

    DECLARE db_cursor CURSOR FOR
        select 'sp_dropserver ''' + [name] + '''' from sys.servers

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @sql   

    WHILE @@FETCH_STATUS = 0
    BEGIN   

           EXEC (@sql)

           FETCH NEXT FROM db_cursor INTO @sql
    END   

    CLOSE db_cursor
    DEALLOCATE db_cursor



############ ARTICLE INFO #############

Article Month: May
Article Date: 09/05/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-delete-all-linked-servers-on-single-server/
Source: http://stackoverflow.com/questions/2015771/delete-all-sql-server-linked-servers-on-single-server
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook