none
using triggers with service broker

    Question

  • Requirement: I am using triggers on a table to send the data from the table to a Queue using Service Broker.

    CREATE MESSAGE TYPE SampleMessage AUTHORIZATION dbo VALIDATION = WELL_FORMED_XML

    CREATE CONTRACT SampleContract AUTHORIZATION dbo (SampleMessage SENT BY INITIATOR);

    CREATE SAMPLEQUEUE WITH STATUS = ON , RETENTION = OFF;

    -- Create init Service CREATE SERVICE SampleServiceInitiator AUTHORIZATION dbo ON QUEUE dbo.SampleQueue (SampleContract);

    -- Create target Service CREATE SERVICE [SampleServiceTarget] AUTHORIZATION dbo ON QUEUE dbo.SampleQueue (SampleContract);

    THIS is part of trigger to actually initiate a dialog.

    DECLARE @Handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @Handle FROM SERVICE [SampleServiceInitiator] TO SERVICE 'SampleServiceTarget' ON CONTRACT [SampleContract] WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @Handle MESSAGE TYPE SampleMessageType;


    I do not have a activation procedure on queue to end the conversation. If I have a stored procedure I can end the conversation as part of SP. It works perfect. where I put the code so that it means the receiver acknowledges the message and move the end point to closed status??? right now the conservation end points are stuck in conversing status.

    Friday, October 25, 2013 8:05 AM

All replies