question

simon-7443 avatar image
0 Votes"
simon-7443 asked simon-7443 commented

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

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

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered simon-7443 commented

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

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

Thank you Erland.
It works as you have described.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

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

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