How to handle this Linked Server error trap in SQL2K?

[Warren]

Below is the script. The problem is when I simulated the Oracle link
drop, my SQL2K never have to a chance to head to the GOTO section as it
dies with this error msg and exit. Any idea on a workround? Thanks.

Server: Msg 7399, Level 16, State 1, Procedure USP_Link_Check, Line 8
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve
service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
----------------------------------------------
ALTER PROCEDURE [USP_Link_Check] AS

DECLARE @myERROR int -- Local @@ERROR
, @myRowCount int -- Local @@ROWCOUNT

--- Verify network connections
select *
from openquery(OraLink,'select count(*) from Oracle.table')
IF @myERROR != 0 GOTO HANDLE_ERROR

HANDLE_ERROR:
Print ' Error in Oracle Link'
RETURN @myERROR
---------------------------------------------

[Erland Sommarskog]

Warren (Nh***@Lieu.org) writes:

Below is the script. The problem is when I simulated the Oracle link
drop, my SQL2K never have to a chance to head to the GOTO section as it
dies with this error msg and exit. Any idea on a workround? Thanks.

Not very many. Error handling in SQL 2000 is a messy story, and many
errors are not catchable in SQL, as the entire batcb aborts. This appears
to be one of these errors.

It all gets better in SQL 2005...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp