Inconsistent Results with TRY - CATCH and Linked Server

Hi
I'm not sure if this is the right Newsgroup for this question, apologies if
it's off topic.
I've got a stored procedure that calls a second stored procedure on a linked
server. My local stored procedure encloses the call to the remote stored
procedure in a try - catch block. I have deliberately introduced an error
into the remote stored procedure to check whether the local try - catch bloc
k
handles it. Unfortunately the try - catch block appears to exhibit
inconsistent behaviour under identical conditions.
At the moment it appears a little like black magic - could anyone explain
what might be going on?
SET UP
--
Here's a snippet of code from the local stored procedure. The code snippet
exhibits the same inconsistent behaviour as the full stored procedure:
LOCAL CODE SNIPPET STARTS
=====================
declare @.DeviceID NVARCHAR(10),
@.RunID NVARCHAR(10),
@.CourierID NVARCHAR(10)
set @.DeviceID = 'TestDev1'
set @.RunID = 'TestRun1'
set @.CourierID = '99999'
DECLARE
@.RetVal INT,
@.StoredProcErrNum INT,
@.SqlErrMsg VARCHAR(200)
SET @.RetVal = 0
SET @.StoredProcErrNum = 0
SET @.SqlErrMsg = N''
DECLARE @.nCID INT
SET @.nCID = CAST(@.CourierID AS INT)
select @.RunID, @.nCID
DECLARE @.fail bit
DECLARE @.Profile TABLE ( profile_desc VARCHAR(40), Street BIT, [Print] B
IT,
t3_run_type CHAR(1) )
SET XACT_ABORT ON
SET @.RetVal = NULL
BEGIN TRY
INSERT INTO @.Profile
EXEC @.RetVal = cme.cme.dbo.spd_t3_login_driver_details @.RunID, @.nCID, @.fail
OUTPUT
END TRY
BEGIN CATCH
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
SET @.RetVal = 1
SET @.StoredProcErrNum = ERROR_NUMBER()
SET @.SqlErrMsg = ERROR_MESSAGE()
END CATCH
IF @.RetVal IS NULL SET @.RetVal = 1
select @.RetVal AS RetVal, @.fail AS Fail, @.StoredProcErrNum AS
StoredProcErrNum, @.SqlErrMsg AS SqlErrMsg
select * from @.Profile
================
CODE SNIPPET ENDS
Here is the remote stored procedure that is called by the above code snippet
:
REMOTE STORED PROCEDURE CODE STARTS
===============================
ALTER PROCEDURE [dbo].[spd_t3_login_driver_details] ( @.run_no VARCHA
R(40),
@.driver_id INT, @.failure BIT OUTPUT )
AS
--select 100/0
IF NOT EXISTS ( SELECT * FROM run r INNER JOIN driver d ON r.default_driver
= d.driver_id
INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
WHERE run_no = @.run_no
AND d.driver_id = @.driver_id )
BEGIN
SELECT @.failure = 1
END
ELSE
BEGIN
SELECT @.failure = 0
SELECT profile_desc, ISNULL(street, 1) AS 'Street', ISNULL(van_print, 1) AS
'Print', ISNULL(t3_run_type, 'M') AS 't3_run_type'
FROM run r INNER JOIN driver d ON r.default_driver = d.driver_id
INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
WHERE run_no = @.run_no
AND d.driver_id = @.driver_id
END
=================
CODE ENDS
The commented line in the remote stored procedure allows me to introduce a
divide by zero error.
The remote stored procedure is pretty simple and I don't expect much trouble
with it. However there are many remote stored procedures running on the
linked server. I just used this one to test whether we could deal with
errors that may arise in any of the remote stored procedures.
Unfortunately we have no control over the remote stored procedures. They
were developed and are maintained by another company, and are running on
their server which we have only limited rights to. We have a test
environment with a duplicate of the production remote server. This is how
I've played around with the remote stored procedure above. However the
production versions of the remote stored procedures are set in stone.
BEHAVIOUR:
--
When I uncomment the select 100/0 and run the alter procedure script for
spd_t3_login_driver_details on the remote server, then run the code snippet
on the local server, this is the behaviour I expect:
The code snippet runs to the end and returns the following recordsets:
(no column name) (no column name)
-- --
TestRun1 99999
RetVal Fail StoredProcErrNum SqlErrMsg
-- -- -- --
1 NULL 8134 Divide by zero error encountered.
profile_desc Street Print t3_run_type
-- -- -- --
<no rows returned>
However, this desired behaviour happens only about one time in ten. Most of
the time when I run the code snippet it aborts when it tries to call the
remote stored procedure.
The following recordset is returned:
(no column name) (no column name)
-- --
TestRun1 99999
I also get the following messages:
(1 row(s) affected)
(0 row(s) affected)
Msg 1206, Level 18, State 118, Line 28
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the
distributed transaction.
ATTEMPTED INVESTIGATION:
--
Initially I thought adding the IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION to
the CATCH block solved the problem by rolling back the implicit transaction.
However I soon discovered the problem came back again. I also thought the SE
T
XACT_ABORT ON might be the problem. I set it to OFF instead. Then the code
snippet seemed to
always run to completion but it was not picking up the error message and
error number from ERROR_MESSAGE() and ERROR_NUMBER().
Can anyone explain the inconsistent behaviour of this code? Why does the try
- catch block work sometimes but not others?
Cheers
SiI don't know why it would be inconsistent but This may be why the
transaction aborts without going to the catch:
Attentions will terminate a batch even if the batch is within the scope of a
TRY.CATCH construct. This includes an attention sent by the Microsoft
Distributed Transaction Coordinator (MS DTC) when a distributed transaction
fails. MS DTC manages distributed transactions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SimonDev" <SimonDev@.discussions.microsoft.com> wrote in message
news:7F48371C-890D-40F9-8E5C-D40C92F4C043@.microsoft.com...
> Hi
> I'm not sure if this is the right Newsgroup for this question, apologies
> if
> it's off topic.
> I've got a stored procedure that calls a second stored procedure on a
> linked
> server. My local stored procedure encloses the call to the remote stored
> procedure in a try - catch block. I have deliberately introduced an error
> into the remote stored procedure to check whether the local try - catch
> block
> handles it. Unfortunately the try - catch block appears to exhibit
> inconsistent behaviour under identical conditions.
> At the moment it appears a little like black magic - could anyone explain
> what might be going on?
> SET UP
> --
> Here's a snippet of code from the local stored procedure. The code snippet
> exhibits the same inconsistent behaviour as the full stored procedure:
> LOCAL CODE SNIPPET STARTS
> =====================
> declare @.DeviceID NVARCHAR(10),
> @.RunID NVARCHAR(10),
> @.CourierID NVARCHAR(10)
> set @.DeviceID = 'TestDev1'
> set @.RunID = 'TestRun1'
> set @.CourierID = '99999'
> DECLARE
> @.RetVal INT,
> @.StoredProcErrNum INT,
> @.SqlErrMsg VARCHAR(200)
> SET @.RetVal = 0
> SET @.StoredProcErrNum = 0
> SET @.SqlErrMsg = N''
> DECLARE @.nCID INT
> SET @.nCID = CAST(@.CourierID AS INT)
> select @.RunID, @.nCID
> DECLARE @.fail bit
> DECLARE @.Profile TABLE ( profile_desc VARCHAR(40), Street BIT, [Print]
> BIT,
> t3_run_type CHAR(1) )
> SET XACT_ABORT ON
> SET @.RetVal = NULL
> BEGIN TRY
> INSERT INTO @.Profile
> EXEC @.RetVal = cme.cme.dbo.spd_t3_login_driver_details @.RunID, @.nCID,
> @.fail
> OUTPUT
> END TRY
> BEGIN CATCH
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> SET @.RetVal = 1
> SET @.StoredProcErrNum = ERROR_NUMBER()
> SET @.SqlErrMsg = ERROR_MESSAGE()
> END CATCH
> IF @.RetVal IS NULL SET @.RetVal = 1
> select @.RetVal AS RetVal, @.fail AS Fail, @.StoredProcErrNum AS
> StoredProcErrNum, @.SqlErrMsg AS SqlErrMsg
> select * from @.Profile
> ================
> CODE SNIPPET ENDS
> Here is the remote stored procedure that is called by the above code
> snippet:
> REMOTE STORED PROCEDURE CODE STARTS
> ===============================
> ALTER PROCEDURE [dbo].[spd_t3_login_driver_details] ( @.run_no VARC
HAR(40),
> @.driver_id INT, @.failure BIT OUTPUT )
> AS
> --select 100/0
> IF NOT EXISTS ( SELECT * FROM run r INNER JOIN driver d ON
> r.default_driver
> = d.driver_id
> INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
> WHERE run_no = @.run_no
> AND d.driver_id = @.driver_id )
> BEGIN
> SELECT @.failure = 1
> END
> ELSE
> BEGIN
> SELECT @.failure = 0
>
> SELECT profile_desc, ISNULL(street, 1) AS 'Street', ISNULL(van_print, 1)
> AS
> 'Print', ISNULL(t3_run_type, 'M') AS 't3_run_type'
> FROM run r INNER JOIN driver d ON r.default_driver = d.driver_id
> INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
> WHERE run_no = @.run_no
> AND d.driver_id = @.driver_id
> END
> =================
> CODE ENDS
> The commented line in the remote stored procedure allows me to introduce a
> divide by zero error.
> The remote stored procedure is pretty simple and I don't expect much
> trouble
> with it. However there are many remote stored procedures running on the
> linked server. I just used this one to test whether we could deal with
> errors that may arise in any of the remote stored procedures.
> Unfortunately we have no control over the remote stored procedures. They
> were developed and are maintained by another company, and are running on
> their server which we have only limited rights to. We have a test
> environment with a duplicate of the production remote server. This is
> how
> I've played around with the remote stored procedure above. However the
> production versions of the remote stored procedures are set in stone.
> BEHAVIOUR:
> --
> When I uncomment the select 100/0 and run the alter procedure script for
> spd_t3_login_driver_details on the remote server, then run the code
> snippet
> on the local server, this is the behaviour I expect:
> The code snippet runs to the end and returns the following recordsets:
> (no column name) (no column name)
> -- --
> TestRun1 99999
> RetVal Fail StoredProcErrNum SqlErrMsg
> -- -- -- --
> 1 NULL 8134 Divide by zero error encountered.
> profile_desc Street Print t3_run_type
> -- -- -- --
> <no rows returned>
> However, this desired behaviour happens only about one time in ten. Most
> of
> the time when I run the code snippet it aborts when it tries to call the
> remote stored procedure.
> The following recordset is returned:
> (no column name) (no column name)
> -- --
> TestRun1 99999
> I also get the following messages:
> (1 row(s) affected)
> (0 row(s) affected)
> Msg 1206, Level 18, State 118, Line 28
> The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> the
> distributed transaction.
> ATTEMPTED INVESTIGATION:
> --
> Initially I thought adding the IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION to
> the CATCH block solved the problem by rolling back the implicit
> transaction.
> However I soon discovered the problem came back again. I also thought the
> SET
> XACT_ABORT ON might be the problem. I set it to OFF instead. Then the code
> snippet seemed to
> always run to completion but it was not picking up the error message and
> error number from ERROR_MESSAGE() and ERROR_NUMBER().
> Can anyone explain the inconsistent behaviour of this code? Why does the
> try
> - catch block work sometimes but not others?
> Cheers
> Si|||Thanks for your reply, Roger.
What exactly is an attention? Sorry if this is a newbie sort of question.
I had assumed that since the severity level of the error was less than 20 it
would have been caught by the catch block. What is the difference between a
n
error and an attention? Books online mentions examples of attentions being
client-interrupt requests and broken client connections but that doesn't
really help me to understand them.
Is there any way of trapping attentions or will they always break T-SQL
code, no matter what the severity level is?
Cheers
Simon
"Roger Wolter[MSFT]" wrote:

> I don't know why it would be inconsistent but This may be why the
> transaction aborts without going to the catch:
> Attentions will terminate a batch even if the batch is within the scope of
a
> TRY.CATCH construct. This includes an attention sent by the Microsoft
> Distributed Transaction Coordinator (MS DTC) when a distributed transactio
n
> fails. MS DTC manages distributed transactions.|||Our DBA pointed out I have missed one very important piece of information:
Although the local server is SQL Server 2005, the linked server (cme) that i
t
calls to is SQL Server 2000 SP4.
Si|||Further progress: I've tried to find information about attention events.
Pretty hard to track down but as I understand them, attention events are jus
t
requests from a client to cancel the currently running query.
I discovered Profiler can track Attention events so ran Profiler traces on
both local and linked servers. There were no attention events raised on
either server when the MS DTC cancelled the distributed transaction. On the
linked server an exception was raised: Error: 8134, Severity: 16, State: 1
(as far as I can recall 8134 is a divide by zero error). On the local serve
r
an exception was raised: Error: 1206, Severity: 18, State: 199. This was
followed by a user error message "The Microsoft Distributed Transaction
Coordinator (MS DTC) has cancelled the distributed transaction.".
Now that it's finally occurred to me to use Profiler I'll continue using it
while playing with SET XACT_ABORT to see if I can make any further progress.
Cheers
Simon