Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 DTS Packages

Author  Topic 

prodigy2006
Yak Posting Veteran

66 Posts

Posted - 2010-02-23 : 10:40:11
Is it possible to find a list of servers on which DTS packages exist?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-23 : 12:10:45
If all the servers are linked from one, you can execute this
Create Table #t (server sysname, dts sysname);

Declare @server sysname
Declare c Cursor
read_only
for
select data_source from sys.servers where is_linked = 1 or data_source = @@servername
open c
fetch next from c into @server
while @@fetch_status = 0
begin
insert #t
EXEC ('SELECT ''' + @server + ''', name FROM [' + @server + '].msdb.dbo.sysdtspackages')
fetch next from c into @server
end
close c
deallocate c

select distinct server from #t
drop table #t
Go to Top of Page
   

- Advertisement -