Check Invalid XML Message in Service Broker Queue

As we know after five failed concurrent
transactions you will get what is known as Message Poisoning after which your queue
will get disabled . Sometimes, it is required to check the invalid messages in
a service broker queue.

Begin try

Declare @vBin varbinary(max)

Declare @pBQuery nvarchar(max)

Declare @MessageXML XML

Declare vCur Cursor for Select Message_Body from dbo.BLOB_AdtQueue_Remote1_IOW

Open vCur

Fetch Next from vCur into @vBin

while @@Fetch_status = 0

begin

   Select @pBQuery = convert(nvarchar(max),@vBin)

   Select @MessageXML = cast(@pBQuery as XML)

   Fetch Next from vCur into @vBin

end

End Try

Begin Catch

   SELECT

   ERROR_NUMBER() AS ErrorNumber,

   ERROR_SEVERITY() AS ErrorSeverity,

   ERROR_STATE() AS ErrorState,

   ERROR_LINE() AS ErrorLine,

   ERROR_PROCEDURE() AS ErrorProcedure,

   ERROR_MESSAGE() AS ErrorMessage,GETDATE(),

   CONVERT(sysname ,USER_NAME())AS DBUSERNAME,

   CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME

   Select @vBin

End Catch

Deallocate vCur