Presently we're using service broker to transmit the messages backwards and forwards, that is working fine. But we desired to group individuals messages using the RELATED_CONVERSATION_GROUP. We desired to use our very own database endured uuid like a RELATED_CONVERSATION_GROUP = @uuid from your database, but despite the fact that we make use of the same uuid each time the conversion_group_id comes different every time we get the queue.
Do everyone know what's wrong with way i'm creating the broker or even the receive call, i've provided both broker creation code and also the receive call code below. Thanks
below may be the code "Service Broker creation code"
CREATE PROCEDURE dbo.OnDataInserted @EntityType NVARCHAR(100), @MessageID BIGINT, @uuid uniqueidentifier, @message_body nvarchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @conversation UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @conversation FROM SERVICE DataInsertSndService TO SERVICE 'DataInsertRcvService' ON CONTRACT DataInsertContract WITH RELATED_CONVERSATION_GROUP = @uuid; SEND ON CONVERSATION @conversation MESSAGE TYPE DataInserted (CAST(@message_body))
below may be the code "Receive code"
WHILE 0 < @@TRANCOUNT ROLLBACK; SET NOCOUNT ON BEGIN TRANSACTION; DECLARE @cID as uniqueidentifier, @conversationHandle as uniqueidentifier, @conversationGroupId as uniqueidentifier, @tempConversationGroupId as uniqueidentifier, @message_body VARBINARY(MAX) RAISERROR ('Awaiting Message ...', 16, 1) WITH NOWAIT ;WAITFOR (RECEIVE TOP (1) @cID = Substring(CAST(message_body as nvarchar(max)),4,36), @conversationHandle = [conversation_handle], @conversationGroupId = [conversation_group_id], @message_body = message_body FROM DataInsertRcvQueue) RAISERROR ('Message Received', 16, 1) WITH NOWAIT Select @tempConversationGroupId = conversationGroupID from ConversationGroupMapper where cID = @cID; declare @temp as nvarchar(max); Set @temp = CAST(@tempConversationGroupId as nvarchar(max)); if @temp <> '' BEGIN MOVE CONVERSATION @conversationHandle TO @tempConversationGroupId; RAISERROR ('Moved to Existing Conversation Group' , 16, 1) WITH NOWAIT END else BEGIN insert into ConversationGroupMapper values (@cID,@conversationGroupId); RAISERROR ('New Conversation Group' , 16, 1) WITH NOWAIT END WAITFOR DELAY '000:00:10' COMMIT RAISERROR ('Committed' , 16, 1) WITH NOWAIT
Our situation is the fact that we have to receive products out of this Service Broker queue inside a loop, obstructing on WAITFOR, and hands them off and away to another system over an hard to rely on network. Products received in the queue are destined for one of several connections to that particular remote system. When the item isn't effectively shipped towards the other system, the transaction for your single item ought to be folded back and also the item is going to be came back towards the queue. We commit the transaction upon effective delivery, unleashing the succession of messages to become acquired with a subsequent loop iteration.
Delays inside a sequence of related products shouldn't affect delivery of unrelated sequences. Single products are sent in to the queue the moment they are offered and therefore are submitted immediately. Products ought to be submitted single-file, though order of delivery even inside a sequence isn't strictly important.
In the loop that receives one message at any given time, a brand new or existing TcpClient is chosen from your listing of open connections, and also the message and also the open connection are passed along although the chain of asynchronous IO callbacks before the transmission is done. Only then do we complete the DB Transaction by which we received the product in the Service Broker Queue.
Just how can Service Broker and conversation groups be employed to help in this?