Why is the activation stored procedure not triggering on my SQL Server QUEUE?

I am new to SERVICE BROKER and QUEUE functionality in SQL Server. I've mostly been trying to follow some examples:

• https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker
• https://www.sqlshack.com/using-the-sql-server-service-broker-for-asynchronous-processing/

I can get the manual processing working just fine. If I call a SP that processes the queue, it works as expected. If I understand it correctly, setting the ACTIVATION, PROCEDURE_NAME for the QUEUE should set this to automatically process messages sent to the queue. However, that is NOT what I am seeing in SQL SERVER v12.0.6433.1

    BEGIN TRANSACTION;

    CREATE TABLE #DONE
    (
        ID INT
    );

    CREATE MESSAGE TYPE CUSTOM_TYPE
        AUTHORIZATION XYZ_USER
        VALIDATION = NONE;
    GO

    CREATE CONTRACT POST_CUSTOM_TYPE_MESSAGE_CONTRACT
        (CUSTOM_TYPE SENT BY ANY);
    GO

    CREATE PROCEDURE TESTING.PROCESS_QUEUE AS
    BEGIN
        DECLARE @HANDLE UNIQUEIDENTIFIER;
        DECLARE @MESSAGE_TYPE SYSNAME;
        DECLARE @MESSAGE INT;

        WHILE (1 = 1)
            BEGIN
                BEGIN TRANSACTION;
                WAITFOR (
                RECEIVE TOP (1)
                    @HANDLE = CONVERSATION_HANDLE,
                    @MESSAGE_TYPE = MESSAGE_TYPE_NAME,
                    @MESSAGE = MESSAGE_BODY FROM TESTING.CUSTOM_QUEUE), TIMEOUT 1000;

                IF (@@ROWCOUNT = 0)
                    BEGIN
                        COMMIT TRANSACTION;
                        BREAK;
                    END

                INSERT INTO #DONE (ID) VALUES (@MESSAGE);
                COMMIT TRANSACTION;
            END
    END
    GO

    CREATE QUEUE TESTING.CUSTOM_QUEUE
        WITH STATUS = ON,
        RETENTION = OFF,
        ACTIVATION (
            PROCEDURE_NAME = TESTING.PROCESS_QUEUE,
            MAX_QUEUE_READERS = 5,
            EXECUTE AS SELF
            ),
        POISON_MESSAGE_HANDLING ( STATUS = OFF );
    GO

    CREATE SERVICE CUSTOM_TYPE_SERVICE
        AUTHORIZATION XYZ_USER
        ON QUEUE TESTING.CUSTOM_QUEUE
        (POST_CUSTOM_TYPE_MESSAGE_CONTRACT);
    GO

    CREATE PROCEDURE TESTING.INSERT_INTO_QUEUE @ID INT AS
    BEGIN
        BEGIN TRANSACTION;

        DECLARE @SERVICE SYSNAME;
        SET @SERVICE = 'CUSTOM_TYPE_SERVICE';

        DECLARE @CONTRACT SYSNAME;
        SET @CONTRACT = 'POST_CUSTOM_TYPE_MESSAGE_CONTRACT';

        DECLARE @HANDLE UNIQUEIDENTIFIER;
        BEGIN
            DIALOG CONVERSATION @HANDLE
            FROM SERVICE @SERVICE
            TO SERVICE @SERVICE
            ON CONTRACT @CONTRACT
            WITH ENCRYPTION = OFF;

        SEND ON CONVERSATION @HANDLE MESSAGE TYPE CUSTOM_TYPE(@ID);
        COMMIT TRANSACTION;
    END
    GO

    EXEC TESTING.INSERT_INTO_QUEUE 1;
    EXEC TESTING.INSERT_INTO_QUEUE 2;
    EXEC TESTING.INSERT_INTO_QUEUE 3;

    SELECT *
    FROM TESTING.CUSTOM_QUEUE;

    SELECT * FROM #DONE;

    ROLLBACK TRANSACTION;

I see 3 records in TESTING.CUSTOM_QUEUE and 0 records in #DONE.

Am I overlooking something here? Or is there a missing DB setting I need? I am wondering if using a custom SCHEMA (instead of DBO) is causing issues as well. Does anyone have any idea what is going on here?

Solution 1:

Closing the loop based on the brief conversation in the comments above, the key observation is that you're inserting into a queue. The processing of that queue by (in this case internal activation) is inherently asynchronous. If you check the queue right after having put items into it, odds are that those items will not have been processed yet. But if you wait some time, whatever is processing the queue should do what it needs to.

Here is a great article explaining how activation happens.