CODEWITHSUNDEEP
sqlsql-server
Jonathan Escobedo
Jonathan Escobedo

Reputation: 4063

Delete All SQL Server Linked Servers on single server

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

Upvotes: 4

Views: 12971

Answers (6)

ajk
ajk

Reputation: 41

Version without a cursor. server_id=0 is the local server not a linked server.

--remove all linked servers    
declare @sql nvarchar(max)

while exists (select 1 from sys.servers where server_id>0)
begin
    select @sql= N'sp_dropserver ' + [name] from sys.servers where server_id>0  
    exec (@sql)
end

Upvotes: 0

Zielyn
Zielyn

Reputation: 1066

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.

Upvotes: 1

gyromonotron
gyromonotron

Reputation: 1121

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

Upvotes: 9

Satya SKJ
Satya SKJ

Reputation: 45

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.

Upvotes: 0

Nathan Wheeler
Nathan Wheeler

Reputation: 5932

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.

Upvotes: 0

davek
davek

Reputation: 22915

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.

Upvotes: 1

Related Questions