SQL Server 2019队列未触发激活存储程序问题求助

场景背景 #

在SQL Server 2019环境中，尝试通过触发器调用队列启动存储过程（实现触发器轻量化），用于生产环境数据处理，对Service Broker队列/消息机制不熟悉。

已执行操作 #

1. 创建队列与服务 #

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)

2. 创建激活存储过程 #

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

3. 手动发送消息测试（模拟触发器逻辑） #

DECLARE @message_body XML;
DECLARE @dlg UNIQUEIDENTIFIER;
-- 构造XML消息体
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 DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService'
    ON CONTRACT [DEFAULT]
    WITH ENCRYPTION = OFF;
-- 通过对话句柄发送消息到队列
SEND ON CONVERSATION @dlg (@message_body);
-- 结束对话会话
END CONVERSATION @dlg;

问题现象 #

• 执行消息发送代码后，tbl_Log无新增记录，确认激活存储过程未触发。
• 已通过以下语句确认队列与激活存储过程关联正常：

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

• 查询系统视图时，sys.transmission_queue和sys.conversation_endpoints有时有记录有时无，曾出现transmission_status为the target service name could not be found、state_desc为DISCONNECTED_OUTBOUND的错误。
• 当前账号拥有db_owner权限，排除权限问题。

解决方案 #

1. 修正服务名称引用问题 #

在BEGIN DIALOG CONVERSATION的TO SERVICE子句中，本地服务需指定服务ID或确保名称完全匹配（Service Broker对名称匹配要求严格）。推荐使用服务ID避免名称匹配问题：

-- 先查询服务ID
SELECT service_id, name FROM sys.services WHERE name = 'StoredProcedureService';

-- 修改对话启动语句，替换为实际service_id
BEGIN DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService', '你的服务ID' -- 新增服务ID参数
    ON CONTRACT [DEFAULT]
    WITH ENCRYPTION = OFF;

2. 完善激活存储过程逻辑 #

激活存储过程必须包含消息接收、处理、会话清理的完整逻辑，否则队列会认为消息未处理，不会再次触发激活。修改后的存储过程示例：

CREATE OR ALTER PROCEDURE dbo.ActivationProcedure
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @dlg UNIQUEIDENTIFIER;
    DECLARE @message_body XML;
    DECLARE @message_type_name NVARCHAR(256);

    -- 循环处理队列中的消息（Service Broker自动控制并发）
    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION;

        -- 接收队列中的消息，超时1秒避免无限等待
        WAITFOR (
            RECEIVE TOP(1)
                @dlg = conversation_handle,
                @message_body = message_body,
                @message_type_name = message_type_name
            FROM dbo.StoredProcedureQueue
        ), TIMEOUT 1000;

        -- 若无消息则退出循环
        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            BREAK;
        END

        -- 处理默认类型消息，后续可扩展调用指定存储过程
        IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DefaultMessage'
        BEGIN
            INSERT INTO tbl_Log (LogType, LogText) 
            VALUES ('test', 'ActivationProcedure触发，消息内容：' + CAST(@message_body AS NVARCHAR(MAX)));
        END

        -- 结束对话清理会话资源
        END CONVERSATION @dlg;

        COMMIT TRANSACTION;
    END
END

3. 检查Service Broker启用状态 #

确保当前数据库已启用Service Broker：

SELECT name, is_broker_enabled FROM sys.databases WHERE name = DB_NAME();

-- 若未启用，执行以下语句（需断开所有数据库连接）
ALTER DATABASE 当前数据库名 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

4. 排查队列状态 #

检查队列是否被禁用或存在错误：

SELECT name, is_receive_enabled, is_enqueue_enabled, status_desc 
FROM sys.service_queues WHERE name = 'StoredProcedureQueue';

确保is_receive_enabled和is_enqueue_enabled均为1，status_desc为NORMAL。

内容的提问来源于stack exchange，提问作者Zakraket