470,874 Members | 1,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,874 developers. It's quick & easy.

How to handle this Linked Server error trap in SQL2K?

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
---------------------------------------------

Jul 23 '05 #1
1 2963
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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jack Smith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.