none
Call procedure after some time

    Question

  • I would like to execute stored procedure after some time from now.
    So, i have created queue and service:

    CREATE QUEUE [dbo].[StartSAQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[p_test_queue] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ), POISON_MESSAGE_HANDLING (STATUS = ON) CREATE SERVICE [SAService] ON QUEUE [dbo].[StartSAQueue]

    CREATE PROCEDURE dbo.p_test_queue
    AS
    DECLARE @Handle UNIQUEIDENTIFIER, @MessageType SYSNAME, @message NVARCHAR(255), @stevec INT;
    RECEIVE TOP (1) @Handle = conversation_handle, @message=message_body FROM dbo.StartSAQueue;

    IF @Handle IS NOT NULL
    BEGIN  
    END CONVERSATION @Handle;
    INSERT INTO test.[dbo].[testQueue]([message]) VALUES(@message)
    END

    Now i start conversation:

    DECLARE @DialogHandle UNIQUEIDENTIFIER;
    DECLARE @RequestMessage XML;
    
    BEGIN dialog CONVERSATION @DialogHandle FROM SERVICE [SAService] TO SERVICE N'SAService', 'current database' WITH ENCRYPTION = OFF;
    
    SET @RequestMessage = N'<Request><SA>58</SA></Request>';
    
    SEND ON CONVERSATION @DialogHandle(@RequestMessage);
    

    It works.
    Now i would like to execute this procedure after 10 minutes:

    BEGIN CONVERSATION TIMER(@DialogHandle) TIMEOUT = 600;

    It works. But how can i add message to this conversation? If I try this, it raises error:

    BEGIN CONVERSATION TIMER(@DialogHandle(@RequestMessage)) TIMEOUT = 600;


    Tuesday, June 24, 2014 11:57 AM