Re: using try catch on linked servers
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 27 Jul 2009 17:06:16 +0000 (UTC)
Roy Goldhammer (roy@xxxxxxx) writes:
i have this code:
begin try
insert table1(data1)
select 'nothing'
exec Linkedserver.db.dbo.farprocedure_sp @dataid = 'id'
end try
begin catch
select @@error
end catch
if there is an error on insert data to table1 the try catch present the
error
however if an error occur on farprocedure_sp
i see it when i run the procedure. but the try catch continue to work with
no error.
why this thing happen and how can i solve it?
Linked servers always mean hassle... But errors in remote procedures
usually fires the local CATCH handler. I've seen some ugly exceptions,
but that's when updating a remote table with four-part notation.
Exactly how does the error message looks like when you run the
linked procedure without TRY-CATCH?
Or do you mean that the remote procedure continues to run after the
error, despite the TRY-CATCH on the local side? In that case, I can
explain. When you run a local procedure that does not have a TRY-CATCH
on its own, but there is a CATCH handler higher up the call stack,
that CATCH handler will intercept execution in the inner procedure.
But this does not apply with linked servers, because you have two
totally disconnected processes. The error in the remote procedure
will trigger the CATCH handler in the local server, but the CATCH
handler cannot intercept execution in the remote server. This is
exactly the same scenario as when a .Net client calls a stored procedure.
An error in the stored procedure will fire a catch handler in C#/VB,
but that catch handler does not affect exectuion in T-SQL.
And in the case of linked servers, the calling server is just like a
client to the other server - which just as well could be Oracle or DB2.
Thus, you need to have a TRY-CATCH handler in the remote procedure as
well.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- using try catch on linked servers
- From: Roy Goldhammer
- using try catch on linked servers
- Prev by Date: Re: using try catch on linked servers
- Next by Date: Insert Statement for SP
- Previous by thread: Re: using try catch on linked servers
- Next by thread: Insert Statement for SP
- Index(es):
Relevant Pages
|
