none
Service Broker: Procedure is not executed

    Question

  • Hi there. I have some troubles with SB. I created stored procedure and the all SB part, however the message appears in the queue but the procedure is executed only sometimes. It executed the first time and executed after some times later. What is wrong?

    The code I use.

    CREATE PROCEDURE dbo.sb_test
    
    AS
      select 'abrakadabra'
      DECLARE @h UNIQUEIDENTIFIER;
      DECLARE @messageTypeName SYSNAME;
      DECLARE @payload VARBINARY(MAX);
      WAITFOR(RECEIVE TOP(1)
        @h = conversation_handle,
        @messageTypeName = message_type_name,
        @payload = message_body
        FROM SBReceiveQueue), TIMEOUT 1000;
    
      INSERT INTO test(a) values(GETDATE());
    GO

    There is the code for SB part:

    -- Create Message Type
    CREATE MESSAGE TYPE SBMessage
    VALIDATION = NONE
    GO
    -- Create Contract
    CREATE CONTRACT SBContract
    (SBMessage SENT BY INITIATOR)
    GO
    -- Create Send Queue
    alter QUEUE SBSendQueue
        WITH STATUS=ON,
        ACTIVATION (
            PROCEDURE_NAME = dbo.sb_test,
            MAX_QUEUE_READERS = 1,
            EXECUTE AS SELF 
             ) ;
    
    GO
    -- Create Receive Queue
    CREATE QUEUE SBReceiveQueue
    GO
    -- Create Send Service on Send Queue
    CREATE SERVICE SBSendService
    ON QUEUE SBSendQueue (SBContract)
    GO
    -- Create Receive Service on Recieve Queue
    CREATE SERVICE SBReceiveService
    ON QUEUE SBReceiveQueue (SBContract)
    GO
    -- Begin Dialog using service on contract
    DECLARE @SBDialog uniqueidentifier
    DECLARE @Message NVARCHAR(128)
    BEGIN DIALOG CONVERSATION @SBDialog
    FROM SERVICE SBSendService
    TO SERVICE 'SBReceiveService'
    ON CONTRACT SBContract
    WITH ENCRYPTION = OFF
    
    SET @Message =  N'AAAAAAAAAAA'; --  CAST(GETDATE() AS VARCHAR);
    SEND ON CONVERSATION @SBDialog
    MESSAGE TYPE SBMessage (@Message)
    GO

    Friday, February 07, 2014 10:29 AM

Answers

  • Hi there. I have some troubles with SB. I created stored procedure and the all SB part, however the message appears in the queue but the procedure is executed only sometimes. It executed the first time and executed after some times later. What is wrong?

    The script you posted shows the proc is activated by messages on SBSendQueue but I believe you intended SBReceiveQueue here.  If you want to send a single message per conversation, the SBReceiveQueue activated proc should execute an END CONVERSATION message after processing the message.  Another activated proc on SBSendQueue should end the other side of the conversation after receiving the resultant EndDialog message to avoid leaking conversation handles.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by dk_____ Monday, February 10, 2014 11:22 AM
    Friday, February 07, 2014 12:06 PM

All replies

  • Hi there. I have some troubles with SB. I created stored procedure and the all SB part, however the message appears in the queue but the procedure is executed only sometimes. It executed the first time and executed after some times later. What is wrong?

    The script you posted shows the proc is activated by messages on SBSendQueue but I believe you intended SBReceiveQueue here.  If you want to send a single message per conversation, the SBReceiveQueue activated proc should execute an END CONVERSATION message after processing the message.  Another activated proc on SBSendQueue should end the other side of the conversation after receiving the resultant EndDialog message to avoid leaking conversation handles.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by dk_____ Monday, February 10, 2014 11:22 AM
    Friday, February 07, 2014 12:06 PM
  • Thanks. I am getting messages right now. I also added END CONVERSATION to the dbo.sb_test procedure (is that the right place?).

    However I did not understand " Another activated proc on SBSendQueue should end the other side of the conversation after receiving the resultant EndDialog message to avoid leaking conversation handles.". What does that mean?

    I also do not understand why is the procedure executed 2 times every time? I am using following code to send the message:

    DECLARE @SBDialog uniqueidentifier
    DECLARE @Message NVARCHAR(128)
    BEGIN DIALOG CONVERSATION @SBDialog
    FROM SERVICE SBSendService
    TO SERVICE 'SBReceiveService'
    ON CONTRACT SBContract
    WITH ENCRYPTION = OFF
    
    SET @Message =  N'AAAAAAAAAAA'; --  CAST(GETDATE() AS VARCHAR);
    SEND ON CONVERSATION @SBDialog
    MESSAGE TYPE SBMessage (@Message)
    

    And I always have 2 records with exactly 1 second difference, for example:

    2014-02-10 07:18:45.607
    2014-02-10 07:18:46.607

    Monday, February 10, 2014 12:20 PM
  • Thanks. I am getting messages right now. I also added END CONVERSATION to the dbo.sb_test procedure (is that the right place?).

    Yes, the target service should end the conversation after processing the message(s).  This will send and EndDialog message back to the initiator queue.

    However I did not understand " Another activated proc on SBSendQueue should end the other side of the conversation after receiving the resultant EndDialog message to avoid leaking conversation handles.". What does that mean?

    The general pattern is that the target service should end the conversation first and the initiator service should end the conversation last, upon receipt of the EndDialog message.

    I also do not understand why is the procedure executed 2 times every time? I am using following code to send the message:

    I can't say without seeing your full script.  Below is a complete example, where the expected single row inserted.

    CREATE TABLE dbo.test(
    	a datetime
    	);
    GO
    
    CREATE PROCEDURE dbo.sb_receive_service
    AS
    DECLARE 
    	 @h uniqueidentifier
    	,@messageTypeName SYSNAME
    	,@payload VARBINARY(MAX);
    
    WAITFOR(RECEIVE TOP(1)
        @h = conversation_handle,
        @messageTypeName = message_type_name,
        @payload = message_body
        FROM dbo.SBReceiveQueue), TIMEOUT 1000;
    IF @@ROWCOUNT > 0
    BEGIN
    	IF @messageTypeName = N'SBMessage'
    	BEGIN
    		INSERT INTO test(a) values(GETDATE());
    		END CONVERSATION @h;
    	END
    	ELSE
    	BEGIN
    		END CONVERSATION @h WITH ERROR = 1 DESCRIPTION = 'Unexpected message type received by target';
    	END;
    END;
    GO
    
    CREATE PROCEDURE dbo.sb_send_service
    AS
    DECLARE 
    	 @h uniqueidentifier
    	,@messageTypeName SYSNAME
    	,@payload VARBINARY(MAX);
    WAITFOR(
    	RECEIVE TOP(1)
    		@h = conversation_handle,
    		@messageTypeName = message_type_name,
    		@payload = message_body
        FROM dbo.SBSendQueue), TIMEOUT 1000;
    IF @@ROWCOUNT > 0
    BEGIN
    	IF @messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    	BEGIN
    	  END CONVERSATION @h;
    	END
    	ELSE
    	BEGIN
    		END CONVERSATION @h WITH ERROR = 1 DESCRIPTION = 'Unexpected message type received by initiator';
    	END;
    END;
    GO
    
    -- Create Message Type
    CREATE MESSAGE TYPE SBMessage
    VALIDATION = NONE
    GO
    
    -- Create Contract
    CREATE CONTRACT SBContract
    (SBMessage SENT BY INITIATOR)
    GO
    
    -- Create Send Queue
    CREATE QUEUE dbo.SBSendQueue
        WITH STATUS=ON,
        ACTIVATION (
            PROCEDURE_NAME = dbo.sb_send_service,
            MAX_QUEUE_READERS = 1,
            EXECUTE AS SELF 
             ) ;
    GO
    
    -- Create Receive Queue
    CREATE QUEUE dbo.SBReceiveQueue
        WITH STATUS=ON,
        ACTIVATION (
            PROCEDURE_NAME = dbo.sb_receive_service,
            MAX_QUEUE_READERS = 1,
            EXECUTE AS SELF 
             ) ;
    GO
    
    -- Create Send Service on Send Queue
    CREATE SERVICE SBSendService
    ON QUEUE dbo.SBSendQueue (SBContract)
    GO
    
    -- Create Receive Service on Recieve Queue
    CREATE SERVICE SBReceiveService
    ON QUEUE dbo.SBReceiveQueue (SBContract)
    GO
    
    -- send a test message
    DECLARE @SBDialog uniqueidentifier
    DECLARE @Message NVARCHAR(128)
    BEGIN DIALOG CONVERSATION @SBDialog
    FROM SERVICE SBSendService
    TO SERVICE 'SBReceiveService'
    ON CONTRACT SBContract
    WITH ENCRYPTION = OFF
    
    SET @Message =  N'AAAAAAAAAAA'; --  CAST(GETDATE() AS VARCHAR);
    SEND ON CONVERSATION @SBDialog
    MESSAGE TYPE SBMessage (@Message)
    GO
    WAITFOR DELAY '00:00:01';
    GO
    --expect 1 row in target table
    SELECT * FROM dbo.test;
    --expect empty target queue
    SELECT * FROM dbo.SBReceiveQueue;
    --expect empty initiator queue
    SELECT * FROM dbo.SBSendQueue;
    --expect closed conversation
    SELECT * FROM sys.conversation_endpoints;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Tuesday, February 11, 2014 3:16 AM