Saturday, October 20, 2012 12:28 PM
Saturday, October 20, 2012 1:14 PM
CREATE PROCEDURE dbo.usp_CleanupQueue1 AS SET NOCOUNT ON; DECLARE @conversation_handle uniqueidentifier = '00000000-0000-0000-0000-000000000000' ,@message_type_name nvarchar(256) ,@message_body varbinary(MAX) ,@description nvarchar(3000); WHILE @conversation_handle IS NOT NULL BEGIN SET @conversation_handle = NULL; WAITFOR ( RECEIVE TOP (1) @conversation_handle = conversation_handle ,@message_type_name = message_type_name ,@message_body = message_body FROM dbo.Queue1) ,TIMEOUT 1000; IF @conversation_handle IS NOT NULL BEGIN IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN --only EndDialog messages are expected. End conversation normally. END CONVERSATION @conversation_handle; END ELSE BEGIN --Unexpected message type may be "http://schemas.microsoft.com/SQL/ServiceBroker/Error" --or some other message type not expected by the initiator. End conversation with error. INSERT INTO dbo.ErrorLog( ErrorTime ,conversation_handle ,message_type_name ,message_body) VALUES( GETDATE() ,@conversation_handle ,@message_type_name ,@message_body) SET @description = 'Unexpected message type ' + @message_type_name + ' received'; END CONVERSATION @conversation_handle WITH ERROR = 1 DESCRIPTION = @description; END; END; END; GO
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Edited by Dan GuzmanMVP Saturday, October 20, 2012 1:22 PM changed queue name
Monday, October 22, 2012 8:23 AMModerator
BEGIN DIALOG alone does not actualy send any message. It just create the initiator endpoint in sys.conversation_endpoints.
When you SEND a message, the message goes at first into sys.transmission_queue. After the SEND is commited, the message is picked up from sys.transmission_queue and delivered to he machines where SERVICE2 is hosted and is enqueued into Queue2. After the enqueue into Queue2 is commited, an ACK is automatically sent back to the host of SERVICE1 and this allows the message to be deleted from sys.transmission_queue.
In the case when SERVICE1 and SERVICE2 are within the same SQL Server instance, we might try to optimize the SEND by directly enqueueing the message into Queue2 (skip the intermediate step of sys.transmission_queue). If this optimization attempt fails for whatever reason (e.g. Queue2 is diasbled), then the normal path of sys.transmission_queue is used even within the same SQL instance (in fcat, even within the same database).
You application cannot send ACK replies, it can only send real message replies. These are ordinary messages sent from target to initiator, and they would follow the exact sequence as above.
Procedure activation (Processqueue2) happens whenever there are available (i.e. unlocked) messages in the queue. It is not a trigger, the procedure does not get activated once for each message. The algorithm that determines when to activate a new instance of the procedure (up to the max of MAX_QUEUE_READERS setting) monitors the activity of the procedure (RECEIVE statements) vs. the incomming rate of messages and determines when the procedure cannot keep up and launches a new instance of it. When you enable a queue, if there are messages in the queue, it will activate the procedure. Same goes for server start-up, database going online etc (if there are messages in the queue, it will activate the procedure).
It is not guaranteed that the activated procedure will actually find messages in the queue. The code of the procedure should always be prepared with being activated but finding the queue empty (altough we do try hard not to activate in such situations).
A one way message flow that does BEGIN DIALOG/SEND/END is at risk of running into problems if the target service suddenly starts erroring dialogs (e.g. permissions change, or service contract changes etc). Because the initiator has already ended the conversation, when the error comes back from the target it will be droped. The initiator has no way of evem knowing the error occured. A much better message exchange patttern is to BEGIN DIALOG/SEND from the initiator, RECEIVE/END from the target, then RECEIVE/END from the initiator (i.e. the target ENDs first). The initiator does not have to sit there waiting for the target to reply, the EndDialog message sent tby the target can be processed by a procedure attached to queue1. This issue is also discussed in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273523&SiteID=1.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
- Marked As Answer by Jason Partner Friday, October 26, 2012 3:44 PM