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

Answer 1

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

Answer 2

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