February 1, 2006 at 9:35 am
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
February 2, 2006 at 11:35 am
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."
February 3, 2006 at 4:28 pm
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