none
No conversation_endpoints

    Question

  • In executing the following code the @token value is NULL when I "select @token = [conversation_id] from sys.conversation_endpoints...

    I am not sure why the conversation is not getting created on "begin dialog conversation".

    ----------------------code------------------------------------------

       declare @conversation_handle UNIQUEIDENTIFIER
       declare @token UNIQUEIDENTIFIER
       declare @xmlBody XML

       begin dialog conversation @conversation_handle
       from service AsyncBrokerService
       to service N'AsyncBrokerService', 'CURRENT DATABASE'
       with encryption = OFF;
               
       select @token = [conversation_id]
       from sys.conversation_endpoints
       where [conversation_handle] = @conversation_handle;
                
       select @xmlBody = (select @procedureName as [name] FOR XML PATH('procedure'), TYPE);
            
       send on conversation @conversation_handle (@xmlBody);

       insert into [tServiceBrokerExecResults]
          ([token], [submit_time])
       values
          (@token, getutcdate());

    ------------------------------------------------------------------------------------------------------------


    John McKelvey

    Wednesday, March 21, 2012 12:05 AM

Answers

  • Hi John,
    I demonstrate the scenario as below, and as expected, the conversation id is returned.

    USE MASTER
    GO
    CREATE DATABASE ServiceBrokerTest
    GO
    USE ServiceBrokerTest
    GO
    -- Enable Service Broker
    ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
    GO
    -- Create Message Type
    CREATE MESSAGE TYPE SBMessage
    VALIDATION = NONE
    GO
    -- Create Contract
    CREATE CONTRACT SBContract
    (SBMessage SENT BY INITIATOR)
    GO
    -- Create Queue
    CREATE QUEUE SBSendQueue
    GO
    -- Create  Service
    CREATE SERVICE AsyncBrokerService
    ON QUEUE SBSendQueue (SBContract)
    GO
    DECLARE @token UNIQUEIDENTIFIER 
    DECLARE @conversation_handle uniqueidentifier
    BEGIN dialog CONVERSATION @conversation_handle
       FROM SERVICE AsyncBrokerService
       TO SERVICE N'AsyncBrokerService', 'CURRENT DATABASE'
       WITH ENCRYPTION = OFF;
                
    SELECT @token = [conversation_id]
    FROM sys.conversation_endpoints
    WHERE [conversation_handle] = @conversation_handle;
    SELECT @token
    ------------------------------------
    3DA5D774-4702-4097-B819-DF177D30F1A6
    (1 row(s) affected)

    For more information, please have a look at this blog: SQL SERVER – Intorduction to Service Broker and Sample Script.
     

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by J-Mac Thursday, March 22, 2012 12:48 PM
    Wednesday, March 21, 2012 1:59 AM

All replies

  • Hi John,
    I demonstrate the scenario as below, and as expected, the conversation id is returned.

    USE MASTER
    GO
    CREATE DATABASE ServiceBrokerTest
    GO
    USE ServiceBrokerTest
    GO
    -- Enable Service Broker
    ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
    GO
    -- Create Message Type
    CREATE MESSAGE TYPE SBMessage
    VALIDATION = NONE
    GO
    -- Create Contract
    CREATE CONTRACT SBContract
    (SBMessage SENT BY INITIATOR)
    GO
    -- Create Queue
    CREATE QUEUE SBSendQueue
    GO
    -- Create  Service
    CREATE SERVICE AsyncBrokerService
    ON QUEUE SBSendQueue (SBContract)
    GO
    DECLARE @token UNIQUEIDENTIFIER 
    DECLARE @conversation_handle uniqueidentifier
    BEGIN dialog CONVERSATION @conversation_handle
       FROM SERVICE AsyncBrokerService
       TO SERVICE N'AsyncBrokerService', 'CURRENT DATABASE'
       WITH ENCRYPTION = OFF;
                
    SELECT @token = [conversation_id]
    FROM sys.conversation_endpoints
    WHERE [conversation_handle] = @conversation_handle;
    SELECT @token
    ------------------------------------
    3DA5D774-4702-4097-B819-DF177D30F1A6
    (1 row(s) affected)

    For more information, please have a look at this blog: SQL SERVER – Intorduction to Service Broker and Sample Script.
     

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by J-Mac Thursday, March 22, 2012 12:48 PM
    Wednesday, March 21, 2012 1:59 AM
  • Your response was appreciated.  I was using the DEFAULT message type and CONTRACT, which should work, but your answer lead me to create my own message type and contract.  This resolved the issue. 

    John McKelvey

    Thursday, March 22, 2012 12:51 PM