DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [Initiator]
TO SERVICE N‘Target’;
SEND ON CONVERSATION @dh;
END CONVERSATION @dh;
Does the code above look familiar? Well, it shouldn’t, because it contains a very nasty error. This is the dreaded fire-and-forget message exchange pattern. The initiator opens a conversation, sends a message and then ends the conversation. I often seen this pattern deployed in practice, and (just apparently!) it seems to works fine. The target receives the message sent, and it ends its own target endpoint of the conversation, and things look happy, happy, happy, joy, joy, joy. Then suddenly something changes, and messages seem to vanish in thin air. The initiator sends them for sure, but the target doesn’t get them. The sent message is not in sys.transmission_queue, is not in the target queue, there is no error in the initiator queue, and even more, the initiator endpoint disappears! What happened? Well, the target service has responded with an error to the message the initiator has sent. The error could happen for a multitude of reasons, most common being one of these:
– the target may had denied access to the initiator service
– the message XML validation has failed (the target may had added a schema validation to the message)
– the target service may no longer implement the contract used by the initiator
– the target service was just dropped (can happen only if the broker instance was specified in the BEGIN DIALOG)
Well, if the target responded with an error, then why isn’t the error enqueued in the initiator’s queue? Because the initiator has declared that it is no interested in seeing that error! It had ended the conversation! So when the error comes back from the target, the error message is dropped and the initiator endpoint is deleted. This is the expected behavior when the conversation endpoint is in DISCONNECTED_OUTBOUND state.
The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:
CREATE PROCEDURE inititator_queue_activated_procedure
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
RAISERROR (N‘Received error %s from service [Target]’, 10, 1, @message_body) WITH LOG;
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
ALTER QUEUE [InitiatorQueue]
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = [inititator_queue_activated_procedure],
EXECUTE AS OWNER);
GO
Now when en error is returned by the target service it is logged into the System Event Log:
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17061
Date: 4/6/2006
Time: 10:01:41 PM
User: REDMOND\remusr
Computer: REMUSR10
Description:
Error: 50000 Severity: 10 State: 1 Received error ?<?xml version=”1.0″?><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8425</Code><Description>The service contract 'DEFAULT' is not found.</Description></Error> from service [Target]
We could have chosen to insert the error message into an audit table, or even send it to an audit service (using Service Broker, of course.J).