We're putting messages into a SQL Server message queue, by a trigger on a table. (When a field is updated, we build some XML, and call the trigger below).
CREATE PROCEDURE [dbo].[up_CarePay_BrokerSendXml]
-- Add the parameters for the stored procedure here
@Data VARCHAR(MAX)
AS
BEGIN
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000)
BEGIN TRY
BEGIN TRAN
BEGIN DIALOG CONVERSATION @InitDlgHandle
FROM SERVICE [//IcmsCarePay/Service/Initiator]
TO SERVICE N'//IcmsCarePay/Service/Target'
ON CONTRACT [//IcmsCarePay/Contract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//IcmsCarePay/Message/Request] (@Data);
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @Message VARCHAR(MAX);
SELECT @Message = ERROR_MESSAGE();
PRINT @Message
END CATCH;
END
This works. A message is placed in the queue.
The message is then sent to the receiving queue on the same server - different database. We then run a proc every minute, which grabs the message from the target queue, and processes it into a staging table for processing. The message is then out of the target queue, and this all works without error.
However...
When I check the initiaitor queue, where the message came from, it's filling up with message.
SELECT TOP 1000 *, casted_message_body =
CASE message_type_name WHEN 'X'
THEN CAST(message_body AS NVARCHAR(MAX))
ELSE message_body
END
FROM [ICMS].[dbo].[IcmsCarePayInitiatorQueue] WITH(NOLOCK)
I'd have thought that when the message went from the initiator, to the target, the initiator would disappear. But it seems to be filling up.
I note that the messages in the initiator have a 'message_type_id' of 2, a 'validation' of 'E' and message body and casted message body are NULL. There all have a message_type_name of 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'.
On the destination database side, here is the procedure used to get the messages from the queue:
CREATE PROCEDURE [dbo].[up_CarePayBrokerReceiveXml]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @XML XML, @Response XML = 'OK', @ConversationHandle UNIQUEIDENTIFIER, @message_type_name SYSNAME, @message_body VARBINARY(MAX), @source_table VARCHAR(100)
DECLARE @Message VARCHAR(MAX), @Line INT, @Proc VARCHAR(MAX), @Exception VARCHAR(MAX)
WHILE ( 1 = 1 )
BEGIN
-- Clear variables, as they may have been populated in previous loop.
SET @message_type_name = NULL
SET @message_body = NULL
SET @ConversationHandle = NULL
SET @source_table = NULL
BEGIN TRY
BEGIN TRAN
WAITFOR ( -- Pop off a message at a time, and add to storage table.
RECEIVE TOP (1)
@message_type_name = message_type_name
, @message_body = message_body
, @ConversationHandle = conversation_handle
, @source_table = CAST([message_body] AS XML).value('(/row/@SourceTable)[1]', 'varchar(50)')
FROM dbo.IcmsCarePayTargetQueue
), TIMEOUT 3000;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK -- Complete the Transaction (Rollback, as opposeed to Commit, as there is nothing to commit).
BREAK
END
-- Code removed for example, but the fields are saved to a staging table in the database here...
-- Respond to Initiator
SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [//IcmsCarePay/Message/Response](@Response);
END CONVERSATION @ConversationHandle;
COMMIT -- End of Transaction
END TRY
BEGIN CATCH
-- End the conversation
END CONVERSATION @ConversationHandle WITH CLEANUP
-- Get details about the issue.
SELECT @Exception = ERROR_MESSAGE(), @Line = ERROR_LINE(), @Proc = ERROR_PROCEDURE(), @Message = 'proc: ' + @Proc + '; line: ' + CAST(@Line AS VARCHAR) + '; msg: ' + @Exception
SELECT @Message -- Displays on Concole when debugging.
-- Log the issue to the Application Log.
INSERT INTO dbo.ApplicationLog
( LogDate ,
Thread ,
Level ,
Logger ,
Message ,
Exception
)
VALUES ( GETDATE() , -- LogDate - datetime
'None' , -- Thread - varchar(255)
'FATAL' , -- Level - varchar(50)
'____up_CarePayBrokerReceiveXml' , -- Logger - varchar(255)
@Message , -- Message - varchar(4000)
@Exception -- Exception - varchar(2000)
)
COMMIT -- We have stored the erronous message, and popped it off the queue. Commit these changes.
END CATCH
END -- end while
END
Why are these messages staying there?
Details of a message that remain in the Initiator queue are:
Status: 1
Priority: 5
queuing_order: 395
mess_sequence_number: 0
service_name: //IcmsCarePay/Service/Initiator
service_contract_name: //IcmsCarePay/Contract
message_type_name: http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
message_type_id: 2
validation: E
message_body: NULL
casted_message_body: NULL