Hallo,
I have some problems to handle the poison messages.
Messages are exchanged between 2 databases on the same SQL Server instance.
I created a Trigger on the insert , that call the sp that begin the conversation(10 minutes Timeout) and send the message to the target queue.
On the target queue I activated a SP and the main code is:
BEGIN TRANSACTION
WAIT FOR (Receive (1) ..) timeout 5000
IF (@.ErrorSave <> 0)
BEGIN
INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES ();
END
ELSE
if (@.messagetypeName = N'Message')
BEGIN
exec [dbo].[sp_ProcessMessage] @.message_body
END
ELSE IF (@.messagetypeName = N'EndOfStream')
BEGIN
END CONVERSATION @.dialog
END
COMMIT TRANSACTION
My communication has a strange behaviour:
if I type
Begin
insert into TriggerTable values(XMLMessage)
insert into TriggerTable values(XMLMessage)
....
insert into TriggerTable values(XMLMessage)
end
Everything work fine but If I write an insert of 1000 message coming from an another table and I use the
cursor login after I while the transmission stop because the Target queue become inactive.
I can see my messages stuck in the Initiator transmission_queue so I think that there is some Poison message that cause 5 rollback and disable the receiver queue.
First I would like to isolate the wrong message and carry on with the insert, my application doesn't have to stop the conversation or return any error, but If I use the Sql server Debugger I'm not able to debug the Target queue's stored procedure.
I suppose that some error happens on the Target queue's stored procedure but how can I first at all find it out?
Maybe it has something to do with the transition wrapped around the RECEIVE command?
thank you for any helps!
Marina B.
If an activated procedure hits and error it will report it to the ERRORLOG file as well as to the systen event log (EventVwr.exe). check there to see if any error is present.
You can determine if poison message happens by either monitoring with Profiler the Broker/Broker:Activation events or subscribying to an event notification (see CREATE EVENT NOTIFICATION in BOL) for the QUEUE_DISABLED event on the queue. But I cannot say how to determine which is the poisson message, this is application specific. You can try to turn off activation and manually run the procedure to see if it works as expected.
|||
Hi,
there was not error on the Event Log so after further investigation I found out that the error was raised by the sp that process the message.
Now I have anyway another small problem because I'm not able to avoid the rolleback on the receiving queue and the disabling of the Target queue.
The SP that process the message raise an Error to the caller (the Target queue's activation SP).
In the BEGIN catch I log the error on a table and I want to continue to process all the messages till the queue is empty.
But it seems that the the Target queue's activation SP always rollback because I can see the the Error is inserted 5 times into the log table and then the Target's queues is disabled.
This are the main parts of the code:
Target's queue activation SP:
Begin try
Begin Transaction
WAITFOR (
RECEIVE top(1) ....)
if (@.@.ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
SET @.ErrorSave = @.@.ERROR ;
IF (@.ErrorSave <> 0)
INSERT INTO tblLOG VALUES(...)
ELSE
exec sp_ProcessMessage @.Message
COMMIT
END TRANSACTION
END TRY
BEGIN CATCH
BEGIN
INSERT INTO tblLOG values(...)
COMMIT
END
END CATCH
This is the exec sp_ProcessMessage @.Message
BEGIN TRY
BEGIN TRANSACTION
... Do something that cause an error
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION ;
END
exec [dbo].usp_RethrowError;
END CATCH
the usp_RethrowError stored procedure raise the error to the caller.
How can I avoid the Rollback in case something goes wrong in the SP that Process the message?
I suppose that in the moment I use a try...catch block in the Target's queue activation SP I have also to delete the statements:
SET @.ErrorSave = @.@.ERROR ;
IF (@.ErrorSave <> 0)
INSERT INTO tblLOG VALUES(...)
because is case of error on the receive command the sp will execute the code in the BEGIN CATCH..END CATCH..isn't?
Thank you
Marina B.
