We are using SQL Service Broker, using two services for initiator and target, then we have a C# service listening and receiving messages from queue.
On SQL Side the setup is this:
CREATE MESSAGE TYPE [http://mycompany.com/MyEventRequestMessage] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [http://mycompany.com/MyEventResponseMessage] VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT [http://mycompany.com/MyMessageContract] ([http://mycompany.com/MyEventRequestMessage] SENT BY INITIATOR,
[http://mycompany.com/MyEventResponseMessage] SENT BY TARGET)
GO
CREATE QUEUE [dbo].[MyEventInitiatorQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY]
GO
CREATE QUEUE [dbo].[MyEventTargetQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY]
GO
CREATE SERVICE [MyEventInitiatorService] ON QUEUE [dbo].[MyEventInitiatorQueue] ([http://mycompany.com/MyMessageContract])
GO
CREATE SERVICE [MyEventTargetService] ON QUEUE [dbo].[MyEventTargetQueue] ([http://mycompany.com/MyMessageContract])
GO
Then if I insert a message:
DECLARE @MessageType NVARCHAR(128) = 'http://mycompany.com/MyEventRequestMessage'
DECLARE @MessageBody NVARCHAR(128) = 'loremipsum'
DECLARE @FromService NVARCHAR(128) = 'MyEventInitiatorService'
DECLARE @ToService NVARCHAR(128) = 'MyEventTargetService'
DECLARE @Contract NVARCHAR(128) = 'http://mycompany.com/MyMessageContract'
DECLARE @conversation_handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE @FromService
TO SERVICE @ToService
ON CONTRACT @Contract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE @MessageType(@MessageBody);
It arrives the target queue as expected.
Then on the service we receive the message rom target queue and perform END CONVERSATION.
The message disapears from target queue, but then a new message is inserted on initiator queue, of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.
Even though our service is prepared to end these conversations, it is listening on the target queue.
How can we clear these end dialog messages? Should we listen also on the initiator queue these kind of messages, in order to end the conversation, or should we do anything else?
We are afraid if this causes any side efect on the database or on the server if no action is made.
Thanks.
I think we found a solution that matches Dan's comment:
CREATE PROCEDURE spHandleInitiatorQueueMessages
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 MyInitiatorQueue), 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 MyInitiatorQueue), TIMEOUT 1000;
END
COMMIT;
GO
ALTER QUEUE MyInitiatorQueue
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spHandleInitiatorQueueMessages,
EXECUTE AS OWNER);
GO
Messages began to actually end