Linked Server - On Connection Error Resume

I have a stored procedure which connects to multiple linked servers. If one connection fails, the stored procedure ends with error.

Is it possible to have the stored proc continue instead of quit?

I have tried the following and none work:
- connect in a different sp
- use sp_executesql

Thanks

Oh yeah...I am using SQL SERVER 2000 SP4 and I know SQL SERVER 2005 has a sp to test a linked server, but I cannot upgrade.

I once had the problem to check for the availbility of linked server. The solution (in our case) was to ping the servers on a regular basis, by using a SQL Server Agent job, which did something like

--INSERT INTO PingTable
--SomeSelectonTheServer, mainly something like SELECT 1, or SELECT * FROM dual (in this case for Oracle)
--UPDATE PingTable, update the status of the ping record to successfull.

Therefore we could query the table for the successfull connectivity, if the column Successfull was not filed or updated properly we threw a manual error.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de