title: Why is the activation stored procedure not triggering on my SQL Server QUEUE?
body: <p>I am new to SERVICE BROKER and QUEUE functionality in SQL Server. I've mostly been trying to follow some examples:</p>
  <ul>
  <li><a href="https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker" rel="nofollow noreferrer">https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker</a></li>
  <li><a href="https://www.sqlshack.com/using-the-sql-server-service-broker-for-asynchronous-processing/" rel="nofollow noreferrer">https://www.sqlshack.com/using-the-sql-server-service-broker-for-asynchronous-processing/</a></li>
  </ul>
  <p>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 <code>ACTIVATION, PROCEDURE_NAME</code> for the QUEUE <em>should</em> 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</p>
  <pre><code>    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;
  </code></pre>
  <p>I see 3 records in <code>TESTING.CUSTOM_QUEUE</code> and 0 records in <code>#DONE</code>.</p>
  <p>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?</p>