SQL queue doesn't start activation procedure SQL Server

SQL Server 2019

I'm trying to start stored procedures from a trigger (so that the trigger stays lean) to process data in some production facility. Working with queues/messages etc is new to me

I've created a queue and service:

CREATE QUEUE [dbo].[StoredProcedureQueue]
CREATE SERVICE [StoredProcedureService] ON QUEUE [dbo].[StoredProcedureQueue];
ALTER QUEUE dbo.StoredProcedureQueue
    WITH ACTIVATION (
        PROCEDURE_NAME = dbo.ActivationProcedure,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER)

The activation procedure is very simple (just for now, it will trigger different stored procedures with parameters later on)

CREATE or ALTER PROCEDURE dbo.ActivationProcedure
AS
BEGIN
    print  'test'
    INSERT INTO tbl_Log (LogType, LogText) values ('test', 'ActivationProcedure')
END

But it's not started

The table trigger would do something like this (but I'm starting this manually):

--------------------
DECLARE @message_body XML;
DECLARE @dlg UNIQUEIDENTIFIER;
-- Construct the XML message body
SET @message_body = '
    <Message>
        <barcode>PS23060094/9.9</barcode>
        <palletweight>993</palletweight>
        <timestamp>' + CONVERT(NVARCHAR(30), GETDATE(), 120) + '</timestamp>
        <processing_procedure>spProcessStep1</processing_procedure>
    </Message>
';
-- Begin a dialog conversation
BEGIN DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService'
    ON CONTRACT [DEFAULT]
    WITH ENCRYPTION = OFF;
-- Send the XML message to the Service Broker queue using the conversation handle
SEND ON CONVERSATION @dlg (@message_body);
-- End the dialog conversation
END CONVERSATION @dlg;

but I'm not getting anything in tbl_log, which indicates that the activation procedure is not triggered.

The activation procedure is linked to the queue

SELECT name, activation_procedure
FROM sys.service_queues
WHERE name = 'StoredProcedureQueue';

When I query

SELECT * FROM sys.transmission_queue
SELECT * FROM sys.conversation_endpoints

I'm not seeing anything, except some times some records, sometimes not. I'm not sure this means my messages are getting processed, but I'm still not seeing the result of the activation procedure in tbl_log

At one point these gave me records, the first ... transmission_status: the target service name could not be found ... and the second ... state_desc: DISCONNECTED_OUTBOUND ...

I am db_owner, so permissions should not be a problem

Anyone that knows what to do? Btw the above code is mostly provided by Chat GPT also

The problem is likely to be that you didn't specify the CONTRACT when creating the Service, ie:

CREATE SERVICE [StoredProcedureService] ON QUEUE [dbo].[StoredProcedureQueue]([DEFAULT]);

Otherwise, the messages will be sent with DEFAULT contract, but your service doesn't support it.

You can probably see the error if you do:

select cast(message_body as xml),*
from StoredProcedureQueue

For me, it returns:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
  <Code>-8408</Code>
  <Description>Target service 'StoredProcedureService' does not support contract 'DEFAULT'.</Description>
</Error>

Another thing, among others, is that you shouldn't END CONVERSATION in your script: END CONVERSATION @dlg; but rather do it in the procedure.

Finally, you need to actually enable Activation by setting: STATUS = ON.

Script for queues + services:

drop SERVICE [StoredProcedureService] 
go
drop QUEUE [dbo].[StoredProcedureQueue]
go
CREATE QUEUE [dbo].[StoredProcedureQueue]
go
CREATE SERVICE [StoredProcedureService] ON QUEUE [dbo].[StoredProcedureQueue]([DEFAULT]);
go

ALTER QUEUE dbo.StoredProcedureQueue
    WITH ACTIVATION (
    STATUS = ON,
        PROCEDURE_NAME = dbo.ActivationProcedure,
        MAX_QUEUE_READERS = 1,EXECUTE AS OWNER
        )
GO

Logging code:

drop table tbl_Log 
create table tbl_Log (ID INT IDENTITY PRIMARY KEY, logtype nvarchar(100), logtext nvarchar(max), dateCreated datetime not null default getdate())

Procedure code:

CREATE PROCEDURE dbo.ActivationProcedure
AS
BEGIN
    INSERT INTO tbl_Log (LogType, LogText) values ('test', 'ActivationProcedure')
     DECLARE @conversation_handle UNIQUEIDENTIFIER;
  DECLARE @message_body XML;
  DECLARE @message_type_name sysname;
     WHILE (1=1)
  BEGIN
    BEGIN TRANSACTION;

    WAITFOR
    (
      RECEIVE TOP (1)
        @conversation_handle = conversation_handle,
        @message_body = CAST(message_body AS XML),
        @message_type_name = message_type_name
      FROM StoredProcedureQueue
    ), TIMEOUT 5000;

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

    IF @message_type_name = N'DEFAULT'
    BEGIN
      -- If necessary handle the reply message here
      DECLARE @AccountNumber NVARCHAR(MAX) = @message_body.value('(Message/timestamp)[1]', 'nvarchar(max)');
    print @AccountNumber;
     -- Not needed if you have a service without reply
     SEND ON CONVERSATION @conversation_handle
        ('<ok>OK</ok>');
      -- Since this is all the work being done, end the conversation to send the EndDialog message
      END CONVERSATION @conversation_handle;
    END

    -- If end dialog message, end the dialog
    ELSE IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @conversation_handle;
    END

    -- If error message, log and end conversation
    ELSE IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @conversation_handle;
    END

    COMMIT TRANSACTION;
  END
END

Test sending code:

DECLARE @message_body XML;
DECLARE @dlg UNIQUEIDENTIFIER;
-- Construct the XML message body
SET @message_body = '
    <Message>
        <barcode>PS23060094/9.9</barcode>
        <palletweight>993</palletweight>
        <timestamp>' + CONVERT(NVARCHAR(30), GETDATE(), 120) + '</timestamp>
        <processing_procedure>spProcessStep1</processing_procedure>
    </Message>
';

-- Begin a dialog conversation
BEGIN DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService'
    ON CONTRACT [DEFAULT] -- Important!
    WITH ENCRYPTION = OFF;
-- Send the XML message to the Service Broker queue using the conversation handle
SEND ON CONVERSATION @dlg (@message_body);