Dot Net Tips and Tricks

I ran into the problem where I would send messages to the a remote service and the xml validation would fail. After checking the intiator side I couldn’t find an error message being returned from the receiving end. No event logs, nothing inserted into my error table… nothing. This got me digging around and I found a serious bug in my code.

I had purchased the Rational Guide to Service Broker 2005 by Roger Wolter and his pattern details the following:

DECLARE @conversationHandle uniqueidentifier

Begin Transaction

– Begin a dialog to the Order Service
BEGIN TRY
BEGIN DIALOG @conversationHandle
FROM SERVICE [TestCarolina_Message_Service]
TO SERVICE ‘Carolina_Message_Service’
ON CONTRACT [CarolinaOnly_Contract]
WITH ENCRYPTION = OFF, LIFETIME = 600;

– Send a message on the dialog
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [msg_to_carolina]
(@Message)

END CONVERSATION @conversationHandle

END TRY
BEGIN CATCH
ROLLBACK
INSERT GENERAL.ERRORS (ERROR)
VALUES( ERROR_MESSAGE())
– Reenable queue
ALTER QUEUE TestCarolina_Message_Queue WITH STATUS = ON
END CATCH

commit

There is one glaring problem with this pattern. If an error happens on the receiving side, the conversation has already been ended on the sender’s side. When the Error is sent from the receiver, the message gets dropped, No EventLog, nothing. The conversation that the receiver is sending on does not exist on the sender anymore.

This leaves the question, how do we cleanup the conversation? Allow the receiver to end the conversation rather than explicitly end in on the sender. This requires the addition of an activated procedure on the sender’s queue that will handle the end conversations, but it adds the ability to handle error messages also. The following in pattern would be the correct way:

CREATE PROCEDURE usp_Message_Queue_Activation
AS
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N’http://schemas.microsoft.com/SQL/ServiceBroker/Error’
BEGIN
INSERT Service_Broker_Errors
(error, [message]) values(N’Received error %s from service [Target]‘, @message_body);
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]
), TIMEOUT 1000;
END
COMMIT;
GO

– Create queue for messages to go to
CREATE QUEUE Test_Message_Queue
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = [usp_Message_Queue_Activation],
EXECUTE AS OWNER);
GO

This would allow errors that occur during transmission to be handled gracefully.