BEGIN TRY... BEGIN DIALOG... Issue

Question

We have some service broker code like this where we had flubbed the SERVICE name in the BEGIN DIALOG statement, and it was leaving the transaction uncommitted when it crashed on the bad SERVICE name.  When we fixed the SERVICE name, it ran fine.

declare @err int = 0, @beginTRANCOUNT int, @retval int = 0;
declare
  @InitDlgHandle uniqueidentifier,
  @XMLPayload xml
;
set @beginTRANCOUNT = @@TRANCOUNT; begin transaction;
  BEGIN DIALOG @InitDlgHandle
    FROM SERVICE [//xxxOurCause.org/SQLSvcBkr/Gizmo/Service/Initiator]
    TO SERVICE N'//OurCause.org/SQLSvcBkr/Gizmo/Service/Target'
    ON CONTRACT [//OurCause.org/SQLSvcBkr/Gizmo/Contract/Widget/v01]
    WITH
      ENCRYPTION = ON
  ;
  set @err += @@ERROR; if (@err <> 0) goto TranExit;
  SEND ON CONVERSATION @InitDlgHandle
    MESSAGE TYPE [//OurCause.org/SQLSvcBkr/Gizmo/MsgType/Widget/Request]
      (@XMLPayload)
  ;
  set @err += @@ERROR; if (@err <> 0) goto TranExit;
  END CONVERSATION @InitDlgHandle;
  set @err += @@ERROR; if (@err <> 0) goto TranExit;
  TranExit:
  if (@err = 0) begin
commit transaction;
  end;
  else begin
if (@beginTRANCOUNT = 0) rollback transaction else commit transaction;
  end;

So, we figured we would put it in a BEGIN TRY... END CATCH block like this to be able to properly manage transaction state.  We ended up with code like this:

declare @err int = 0, @beginTRANCOUNT int, @retval int = 0;
declare
  @InitDlgHandle uniqueidentifier,
  @XMLPayload xml,
  @ErrMsg nvarchar(2048)
;
set @beginTRANCOUNT = @@TRANCOUNT; begin transaction;
  save transaction SendMsg;
  begin try
    BEGIN DIALOG @InitDlgHandle
      FROM SERVICE [//xxxOurCause.org/SQLSvcBkr/Gizmo/Service/Initiator]
      TO SERVICE N'//OurCause.org/SQLSvcBkr/Gizmo/Service/Target'
      ON CONTRACT [//OurCause.org/SQLSvcBkr/Gizmo/Contract/Widget/v01]
      WITH
        ENCRYPTION = ON
    ;
    SEND ON CONVERSATION @InitDlgHandle
      MESSAGE TYPE [//OurCause.org/SQLSvcBkr/Gizmo/MsgType/Widget/Request]
        (@XMLPayload)
    ;
    END CONVERSATION @InitDlgHandle;
  end try
  begin catch
    rollback transaction SendMsg;
    set @ErrMsg = ERROR_MESSAGE();
    raiserror(@ErrMsg, 16, 1);
    set @err += @@ERROR;
  end catch;
  TranExit:
  if (@err = 0) begin
commit transaction;
  end;
  else begin
if (@beginTRANCOUNT = 0) rollback transaction else commit transaction;
  end;

However, when we did this, we were still being left with an open transaction.  So we updated the CATCH block to have some output.  "In catch..." was never output!  Apparently, not even a BEGIN TRY... END CATCH block can handle a bad SERVICE name on a BEGIN DIALOG statement.  Am I missing something here, or should I post an error to SQL Server Connect?

  begin catch
    rollback transaction SendMsg;
    raiserror(N'In catch...', 10, 1) with nowait;
    set @ErrMsg = ERROR_MESSAGE();
    raiserror(@ErrMsg, 16, 1);
    set @err += @@ERROR;
  end catch;

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org

Answer 1

I suggest you add SET XACT_ABORT ON in T-SQL batches that contain explicit transactions the help ensure transactions are rolled back following errors.  Although it's not perfect, it will protect you from errors like this.  See http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

if your error was in how you coded the FROM_SERVICE...then your target service probably has a message sit'n in some transmission queue try'n to figure out how to get the end dialog, or whatever reply message back to this  undefined client.  This isn't an error...nothing would trip the try/catch block.

---

mike t.

Answer 3

I suggest you add SET XACT_ABORT ON in T-SQL batches that contain explicit transactions the help ensure transactions are rolled back following errors.  Although it's not perfect, it will protect you from errors like this.  See http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Dan,

Actually, this code is in a stored procedure, and the stored procedure is in a set of nested calls, about 4 deep when this proc gets called.  Each of the procs has its own transaction code (That is why I have the "if (@beginTRANCOUNT = 0) rollback transaction else commit transaction;" for the (@err <> 0)condition.).

If I "SET XACT_ABORT ON" in this procedure, will it abort the whole nested transaction set or just the one in this procedure?  The bigger transaction that this is a part of must succeed, even if the starting the Service Broker conversation fails.  That was kind of the point of using Service Broker - isolating the critical steps from good-to-have steps.

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org

Answer 4

I have posted this as a Connect issue for SQL Server 2012 RC0:

BEGIN DIALOG Error Not Caught by TRY-CATCH
https://connect.microsoft.com/SQLServer/feedback/details/730128/begin-dialog-error-not-caught-by-try-catch

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org

Answer 5

If I "SET XACT_ABORT ON" in this procedure, will it abort the whole nested transaction set or just the one in this procedure?  The bigger transaction that this is a part of must succeed, even if the starting the Service Broker conversation fails.  That was kind of the point of using Service Broker - isolating the critical steps from good-to-have steps.

XACT_ABORT ON will terminate the batch so it won't work in your scenario.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Hi,

I got a reply back in Connect from the SQL Server team:

========================================================

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

- Compile errors, such as syntax errors, that prevent a batch from running.

- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution

These errors will be caught when executed at a lower level than the TRY CATCH block (e.g. in a stored proc or via sp_executesql).

More info at http://msdn.microsoft.com/en-us/library/ms175976.aspx

========================================================

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org