Mark Jones (9/4/2009)
Good solution, but in a world of non perfect trusted domains the linked server solution simply has too many issues to be viabale. Because you only open a single process within a stored procedure that runs agaginst all servers in your set, if a problem is encountered running remote SQL through linked servers the entire process will fail and end. for example:If you have 10 servers you will query if server 2 fails, then you will miss the remaining 8 servers.
Not necessarily, you could use sp_testlinkedserver and a bit of error handling to trap any connectivity issues before firing any queries at the linked server.
http://msdn.microsoft.com/en-us/library/ms189809.aspx
Personally I prefer an Integration Services approach, using a package variable populated from a lookup table to loop through and connect to each server in turn.
Chris