"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 VARCHAR(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

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