How To Find Out What It The The Poison Message.

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.