I have a Stored Procedure in a database on a central server, which uses nested cursors (unfortunately - I don't see an alternative) to loop through all linked servers recognised by the central server (from master.sys.servers) and on each linked server loop
through all databases looking for a specific table and, if it exists, inserting data from it into a table in the central server database. Of course, it is slow (just under an hour for 30+ linked servers averaging well over 100 databases), but I would
expect to run it only two or three times a year.
My problem is that the people responsible for setting up the linked server connections are not good at removing the link when a server is removed; there are also other cases where my SP is unable to connect to a specific linked server.
Within the Stored Procedure, is there any way to prevent the SP from crashing and instead detect a failure to connect to a linked server and pass on to the next one after using error handling to provide a report of failures?
All servers are running SQL Server 2005, and connection uses four-part names.
View Complete Post