为什么激活存储过程不会在我的SQL Server QUEUE上触发

Question

我是SQL Server中SERVICE BROKER和QUEUE功能的新手。我一直在尝试遵循一些例子：

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

我可以让手动处理正常工作。如果我调用处理队列的SP，它将按预期工作。如果我理解正确，为QUEUE设置ACTIVATION, PROCEDURE_NAME应该将其设置为自动处理发送到队列的消息。但是，这不是我在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;

我在TESTING.CUSTOM_QUEUE中看到3条记录，在#DONE中看到0条记录。

我是不是在俯瞰什么？或者我需要缺少数据库设置吗？我想知道使用自定义SCHEMA(而不是DBO(是否也会导致问题。有人知道这里发生了什么吗？

Answer 1

根据上面注释中的简短对话结束循环，关键的观察结果是您正在插入队列。(在本例中为内部激活(对该队列的处理本质上是异步的。如果你在放入物品后立即检查队列，那么这些物品很可能还没有被处理。但是，如果您等待一段时间，不管是什么在处理队列，都应该做它需要做的事情

这是一篇很好的文章，解释了激活是如何发生的。