CONVERSATION TIMER CHECK IF EXISTS AND REMOVE

Question

I would like to execute stored procedure after an hour and I do it like this:

CREATE QUEUE [dbo].[SalesReturnQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[p_salesReturn] , 
MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY] 
GO


CREATE SERVICE [SalesReturnService]  ON QUEUE [dbo].[SalesReturnQueue] ([DEFAULT])
GO


DECLARE @dialogHandle UNIQUEIDENTIFIER, @messageTypeName sysname, @messageBody varbinary(max);

BEGIN dialog CONVERSATION @dialogHandle FROM SERVICE [SalesReturnService] TO SERVICE N'SalesReturnService', 'current database' WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER(@DialogHandle) TIMEOUT = 3600;

It works. After one hour my procedure is activated.
Now I would like to check if there is already some activation waiting to be executed after some time?
How can I get it and how can I remove it?

Answer 1

Work from this:

DECLARE @service_name sysname = 'SalesReturnService'
SELECT ce.*
FROM   sys.conversation_endpoints ce
JOIN   sys.services s ON ce.service_id = s.service_id
WHERE  s.name = @service_name
  AND  ce.far_service = @service_name
  AND  ce.dialog_timer > sysutcdatetime()

You may have to fine-tune a little. I'm uncertain what is in the column dialog_timer when there is no conversation set up. (It is not NULL, if the documentation is to be believed.)

To remove the timer, use END CONVERSATION with the conversation_handle column from the query above.

Answer 2

Hi @simon ,

ErlandSommarskog has provided you with a solution, please try and update the problem.

Regards
Echo