Loop through servers ignoring connection failures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Loop through servers ignoring connection failures

First of all, I’d like to ask you to forgive me if this is a repost, I tried to search the forum but got Timeout Expired errors. I also checked the 9 first pages of this section for a similar thread, but found none.<br /><br />Let’s go to the question… [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />I have a table with several server names and I select all of them into a global variable rowset and loop through it in my DTS package, changing the connection object to connect to all servers inside the rowset. Happens that sometimes one of these servers is down and then my whole package fails because of that offline server. <br />What I’m looking for is a way to handle these connection failures and keep looping through the rowset and just log the failed server name.<br /><br />Thanks in advance for any thoughts.<br /><br /><br />Regards,<br /><br />Fernando Toledo
If you are performing this in DTS you have a couple of options: 1) If you have multiple tasks you can change the workflow to be "On Completion" rather than "On Success". That way if an error happens it will continue on. 2) You can set the maximum error properties on the DTS package. This will allow x failures and continue on. Third option is you use SQL 2005 – inside the loop use TRY… CATCH and ignore the failures. Hope this helps. Simon