Dropping all linked Servers

  • Hi

    1) Is there a way to drop all linked servers ? I have tried putting together a cursor which doesnt work:

    declare @server_name varchar(256)

    DECLARE linked_servers CURSOR FOR

    select srvname from sysservers where srvid <> 0

    open linked_servers

    fetch next from linked_servers  into @server_name

    while @@fetch_status = 0

    begin

      print 'Disconnecting from ' + @server_name

      sp_dropserver @server_name

     

      fetch next from linked_servers  into @server_name

    end

     

    CLOSE linked_servers

    DEALLOCATE linked_servers

    2) Also Is there a way to add all all the servers registered in Enterprise manager as linked servers ?

    3) even more specifically, I am trying to extract SQL Server system informatin from all registered SQL Server instances.

    Regards

  • As for automating the task(s) that you mention, it is possible. But in order to assist you will have to post the error(s) you are getting.

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • quick & dirty answer below

    note: I don't use cursor very often and the datatypes are most likely not the best.

    Tim S

    declare @server_name varchar(256), @server_id int, @rmtloginame varchar(256), @loginame varchar(256)

    DECLARE linked_servers CURSOR FOR

    select srvname, srvid from sysservers where srvid 0

    open linked_servers

    fetch next from linked_servers into @server_name, @server_id

    while @@fetch_status = 0

    begin

    DECLARE linked_servers_logins CURSOR FOR

    select rmtloginame, SUSER_SNAME ( loginsid) as loginame

    from sysoledbusers where rmtsrvid = @server_id

    AND rmtloginame IS NOT NULL

    open linked_servers_logins

    fetch next from linked_servers_logins into @rmtloginame, @loginame

    while @@fetch_status = 0

    begin

    print 'Disconnecting remote login ' + COALESCE(@rmtloginame, 'NULL') + ' FROM ' + COALESCE(@server_name, 'NULL')

    EXEC sp_droplinkedsrvlogin @rmtsrvname = @server_name , @locallogin = @loginame

    fetch next from linked_servers_logins into @rmtloginame, @loginame

    end

    CLOSE linked_servers_logins

    DEALLOCATE linked_servers_logins

    print 'Disconnecting from ' + @server_name

    EXEC sp_dropserver @server_name

    fetch next from linked_servers into @server_name, @server_id

    end

    CLOSE linked_servers

    DEALLOCATE linked_servers

Viewing 3 posts - 1 through 2 (of 2 total)

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