Delete All SQL Server Linked Servers on single server

Question

Hello, Im 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.

Thanks

Answer 1

I think you can only delete on linked server at a time, using sp_dropserver:

http://msdn.microsoft.com/en-us/library/ms174310.aspx

You could call sp_linkedservers

http://msdn.microsoft.com/en-us/library/ms189519.aspx

reading the returned list into a temporary table, and then loop through that table, calling sp_dropserver for each element.

Answer 2

You can't just arbitrarily delete all the linked servers from every server. You'll have to (at a minimum) open a connection to each server and run some form of script or command for each linked server. If you want to know how to write a script to drop all linked servers, I suggest that you start by looking at sp_linkedservers and sp_dropserver.

Answer 3

While connected to the server:

select 'exec sp_dropServer ''' + name + '''', * from sys.servers where is_linked = 1

run the outputted first column. win.

This also allows you to pick and choose which server you want to get rid of. It is probably hanging on the connection, getting garbage it doesn't know how to handle back from a server, or a corrupted link driver.

Answer 4

Do you get any error message when it is crashing? Also what is the service pack of that corresponding SQL 2000 server?

I would rather fix this tools issue than simply recreating them a fresh.

Answer 5

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