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.
What’s the error message you received? I found you used one queue as both of receive queue and send queue, I suggest creating two different queues. You can refer to the codes on the following link. If there is any error message, please post here for analysis.
SQL SERVER – Introduction to Service Broker and Sample Script
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.
what I do is setup another server that is the 'client' to the service you are calling. Then install a activation proc on the client queue so when your target service sends the 'end dialog' back, the client Q will fire the 'ghost conversation cleanup' proc...which does nothing other than issue a 'end con' too.
so your 'From Service [SampleServiceInitiator] above would be: 'from service: [SampleServiceInitiatorCli].
and your activation proc on the client queue just does a standard waitfor recv...then calls: end conversation @con