none
BEGIN TRY... BEGIN DIALOG... Issue

    问题

  • 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

    2012年3月3日 1:37

答案

全部回复

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

    2012年3月3日 2:15
  • 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.

    2012年3月3日 20:12
  • 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

    2012年3月3日 21:35
  • 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

    • 已标记为答案 JediSQL 2012年3月12日 16:37
    2012年3月12日 16:37
  • 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/

    2012年3月13日 2:38
  • 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

    2012年3月26日 18:19