Hello,
when is seemd that everything works some weird behaviours comes out.
I try to summarize the problem without to post the complete code.
Service Broker is set to have a dialog between two databases on the same SQL Server instance.
The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.
The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).
The conversation has a Timeout Dialog to end the dialog after a while.
The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.
n.b.the Receive is within a Transation that I commit at the end.
some other informations that in the meanwhile I found out :
This was my complete WAIT FOR(RECEIVE :
WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@.message_type=message_type_id, --the type of message received
@.messagetypename=message_type_name,
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;
if (@.@.ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@.@.ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!
Any ideas?
Thank you!
M.B.
Thank you very much
M.B.
This sounds like there are messages constantly comming and causing the WAITOFR(RECEIVE...) to get another message while is waiting. Is this the case?|||Hi,
it shouldn't be!!
I suppose that the only way to understand what is going on is to use the SQL Profiler.
Isn't?
Thank you
|||Hi,
I tried to use the SQL server Profiler to understand what is going on when I send/receive the message.
First at all on both queues(initiator and target) I have RETENTION = OFF so the check if @.@.ROWCOUNT = 0 will be never true because the message stays in the queue till the END of the CONVERSATION and in my case the conversation will end when there is an error or when the conversation timeout expires; otherwise I send/receive using the same conversation handle.
If I write:
WAITFOR (
RECEIVE top(1) ...
), TIMEOUT 5000;
In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is constantly executed.
If I write:
WAITFOR (
RECEIVE top(1) ...
)
In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is executed only ONCE.
I'm quite confuse ... I thought that is better to use a timeout but if I use I have that strange behaviour!!
Any helps/advices?
Thankx
Marina B.
@.@.ROWCOUNT is set by RECEIVE to the number of rows (messages) actually returned, so it doesn't matter if RETENTION is ON or OFF.
From your description of the problem is clear that your procedured does not correctly detect when RECEIVE returned no rows, so it continues to spin on empty RECEIVEs that timeout. Can you post the actual code of the procedure?
|||Hi Remus,
thank you very much for your answer...
Maybe it is not necessarely but this is the SP wuch start the conversation (activated by a trigger on a insert )
PROCEDURE [dbo].[sp_sendInserted]
@.MessageXML nvarchar(max)
AS
BEGIN
DECLARE @.dialog_handle uniqueidentifier;
DECLARE @.dialog_id uniqueidentifier;
DECLARE @.msg XML;
DECLARE @.Error INT;
SET NOCOUNT OFF;
BEGIN TRY
BEGIN TRANSACTION;
set @.msg = Convert(xml,@.MessageXML);
WHILE (1=1)
BEGIN
set @.dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceivedService');
if @.dialog_handle is null
--Begin new dialog
BEGIN
BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SendService]
TO SERVICE 'ReceivedService'
ON CONTRACT [MainContract]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@.dialog_handle) TIMEOUT = 600;
END;
SEND ON CONVERSATION @.dialog_handle
MESSAGE TYPE MyMessage (@.msg); END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
BEGIN
ROLLBACK TRANSACTION;
END
Insert into [dbo].[tblErrorXMLMessages] values(@.MessageXML,@.dialog_handle,@.dialog_id,null,null,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_PROCEDURE(),getdate());
END CATCH
END
And this is the SP activated on the ReceiveQueue:
PROCEDURE [dbo].[OnReceivedMessage]
AS
DECLARE @.message_type INT;
DECLARE @.messagetypename NVARCHAR(1000);
DECLARE @.XMLmessage_body XML;
DECLARE @.dialog UNIQUEIDENTIFIER;
DECLARE @.dialog_id UNIQUEIDENTIFIER;
DECLARE @.ErrorSave INT;
DECLARE @.ErrorDesc NVARCHAR(100);
SET NOCOUNT ON;
WHILE (1 = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@.message_type=message_type_id, --the type of message received
@.messagetypename=message_type_name,
@.XMLmessage_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [ReceivedQueue]
)--, timeout 5000; -- if the queue is empty for three second
--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages
set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);
-- If we didn't get anything, bail out
if (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');
COMMIT;
BREAK;
END
If (@.messagetypeName = N'MyMessage')
BEGIN
DECLARE @.ret integer;
EXECUTE @.ret = [TestReceiver].[dbo].[sp_ShredXMLMessageToRelationalData] @.XMLmessage_body; --SP that PROCESSES THE MESSAGE RECEIVED ON THE QUEUE
if (@.ret <> 0)
INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES
(cast(@.XMLmessage_body as nvarchar(max)),@.dialog,@.dialog_id,null,null,0,null,null,getdate());
END
ELSE IF (@.messagetypeName = N'EndOfStream')
BEGIN
END CONVERSATION @.dialog;
END
-- Check for the Error Dialog message.
ELSE IF(@.messagetypeName=N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
DECLARE @.WrongXMLMessage NVARCHAR(max);
set @.WrongXMLMessage = (Select message_body FROM dbo.TradeReceivedQueue WITH (NOLOCK) where Message_type_name = 'MyMessage' and status = 3 and conversation_handle=@.dialog);
DECLARE @.Error int;
DECLARE @.ErrorDescription nvarchar(4000);
WITH XMLNAMESPACES
('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb)
SELECT @.Error = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssb:Code)[1]','INT'),
@.ErrorDescription = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssb
IF @.WrongXMLMessage is null
BEGIN
SET @.WrongXMLMessage = N'<Error>Error retrieving the wrong XML message from the sender queue</Error>';
END
Insert into [TestSender].[dbo].[tblErrorXMLMessages] values(@.WrongXMLMessage,@.dialog,@.dialog_id,'MArina','ReceivedQueue',@.Error,@.ErrorDescription,ERROR_PROCEDURE(),getdate());
--After the End Conversation all messages from the queue are deleted
END CONVERSATION @.dialog;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
insert into [testReceiver].[dbo].[tblReceived] values('Error','<Marina>Error in SP Traget QUEUE</Marina>');
END CATCH
END
Let me know whether you need more info regarding contract, queues etc..etc..
Thank you very much!
Marina B.
|||
marina B. wrote:
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@.message_type=message_type_id, --the type of message received
@.messagetypename=message_type_name,
@.XMLmessage_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [ReceivedQueue]
)--, timeout 5000; -- if the queue is empty for three second
--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages
set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);
-- If we didn't get anything, bail out
if (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');
COMMIT;
BREAK;
END
@.@.ROWCOUNT (Transact-SQL)
Returns the number of rows affected by the last statement
RECEIVE is not the last statement before you check @.@.ROWCOUNT.
|||As always,
my stupid mistake.
I thought that I was making a mess with the transaction!!
I moved the @.@.Rowcount check after the RECEIVE statement and it worked..
I should read more CAREFULLY the BOL !!
Thankx!!
Marina B.
