I'm attempting to write a sproc on SERVER1 which tries to execute a query on SERVER2. I need to be able to catch any errors generated because of a comms failure between the two machines in order to take some alternative action. Stripping my sproc back to its bare bones, I'm attempting to do something like the following on SERVER1:

CREATE PROCEDURE dbo.MyProc AS

DECLARE @LocalError int
EXEC('SELECT * FROM SERVER2.master.dbo.sysobjects')
SET @LocalError = @@ERROR

IF @LocalError <> 0
BEGIN
   -- Some other code to call in event of comms failure
END

However, what happens (after a few seconds of SERVER1 trying to contact SERVER2) is that the EXEC statement fails and the sproc exits immediately, without calling the error handling code.

If I modify the actual query to generate a non-comms related error, eg
EXEC ('SELECT * FROM SomeTableThatDoesntExist'), then the error handling is fine.

How you say "EXEC statement fails and the sproc exits immediately"? Does that show any error? It works fine for me.

EXEC('SELECT * FROM SomeTableThatDoesntExist') - should return OLE DB provider 'SQLOLEDB' does not contain table. I am not sure if that triggers error handling, as it fails to process the following lines next to exec()

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay

When I try to execute the SELECT statement for some non-existent table I get the following error message:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'fergegeg'.

But the crucial thing is that after displaying this message the remaining code following the EXEC command also executes, so I can include some error handling code to take an alternative course of action, do any clean up, etc. All well and good.

However, if my error is generated instead by some comms failure, eg I'm trying to SELECT from a table on another machine and my network cable is unplugged, I get the following error.

Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
[OLE/DB provider returned message: Unspecified error]

Unfortunately after getting this error Query Analyser immediately exits the script without executing any of the remaining code - in other words any error handling I might have included gets ignored.

The question is, how do I deal with errors caused by network failure? Or alternatively is there any way of testing a server connection before attempting to run queries against it?

Thanks