CONVERSATION TIMER CHECK IF EXISTS AND REMOVE

simon 61 Reputation points
2021-03-20T05:52:32.077+00:00

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?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2021-03-20T11:08:12.647+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-03-22T07:08:40.463+00:00

    Hi @simon ,

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

    Regards
    Echo

    0 comments No comments