Extracing SQL Service Broker Error Fields RRS feed

  • Question

  • I am creating some error handling for the `'SQL/ServiceBroker/Error'` `MessageType` within my queues `Activation Stored Procedure`.  While, I have information on how to access the following exception fields:

     - Code (e.g. Error Number)
     - Description (e.g. Error Message)

    I am having trouble finding information on how to access (other) 'standard' error fields from the 'SQL/ServiceBroker/Error' schema, fields like:

    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorProcedure VARCHAR(400);
    DECLARE @ErrorLine INT;

    ...I am having trouble finding information on.

    Does anyone know...

     - Where I can get information of the access these fields from the
       'SQL/ServiceBroker/Error' schema?

    For those who need to see code ...

        -- HANDLE ERRORS: for Error MessageTypes
        ELSE IF @MessageTypeName = N''

               -- GET ERROR: Alias Namespace
               WITH XMLNAMESPACES ('' AS ssb)
                         @ErrorNumber = @MessageBody.value('(//ssb:Error/ssb:Code)[1]', 'INT'),
                         @ErrorMessage = @MessageBody.value('(//ssb:Error/ssb:Description)[1]',               'NVARCHAR(MAX)');
                         --@ErrorSeverity = ????,
                         --@ErrorState = ????,
                         --@ErrorProcedure = ????,
                         --@ErrorLine = ????;

            END CONVERSATION @ConversationHandle;


    Monday, January 7, 2019 4:59 PM

All replies

  • It could have helped if you had posted a sample message, so that we know where to look. I tried to find the schema definition, but I was not successful. I was however able to find an old blog post from Remus Rusanu, and the examples there seems to only have code and description, so I guess you are out of luck.

    Erland Sommarskog, SQL Server MVP,

    Tuesday, January 8, 2019 10:46 PM
  • With our Service Broker activation stored procedures we use the TRY...CATCH approach around statement blocks that have a reasonable chance of generating errors specific enough for use to want to track, and an overall TRY...CATCH block that picks up anything we weren't expecting.

    For INSERT (or DELETE or UPDATE) statements we begin with a TRY and, initially, check for specific errors:

                          IF ERROR_NUMBER() IN (1205,2627)--1205 Deadlock, 2627 Primary Key Violation
                                SET @ErrorNumber           = ERROR_NUMBER();
                                SET @ErrorMessage          = ERROR_MESSAGE()
                                EXECUTE [RTM].[AddEvent] 'RETRY'

    We record the error in an event table and then loop to retry (with a max retry value so we don't get stuck.)

    If the error isn't one we're expecting from the code, or the max retry value has been hit, this code records what we need:

        SET @ErrorNumber           = ERROR_NUMBER();
        SET @ErrorSource           = OBJECT_NAME(@@PROCID);
        SET @ErrorLine             = ERROR_LINE();
        SET @ErrorMessage = CONCAT('INSERT',': ',ERROR_MESSAGE())
        SET @ErrorSeverity         = ERROR_SEVERITY();
        SET @ErrorState            = ERROR_STATE();
        SET @ErrorUser             = SUSER_SNAME();
        INSERT INTO RTM.Process_M204_ErrorLog

    That gives us the Error number, severity, the name of the stored procedure throwing the error, and even the offending message. Our Error_Date field is filled with GETDATE() so we have the error time to the millisecond if needed.

    Kevin J Mackey

    Friday, July 5, 2019 3:24 PM