CONVERSATION TIMER CHECK IF EXISTS AND REMOVE

Question

question

simon-7443 asked Mar 20, '21 simon-7443 commented Mar 24, '21

CONVERSATION TIMER CHECK IF EXISTS AND REMOVE

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?

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-20T11:08:12.647Z

ErlandSommarskog answered Mar 20, '21 simon-7443 commented Mar 24, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

simon-7443 · Mar 24, 2021 at 10:36 AM

Thank you Erland.
It works as you have described.

0 ·

Answer 2 · 2021-03-22T07:08:40.463Z

EchoLiu-msft answered Mar 22, '21

Hi @simon-7443,

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

Regards
Echo

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question