ACK showing up as Orphaned Message
Could someone explain how and why a HL7 ACK be showing up as a Orphaned message when I run the following SQL...
SELECT HL7.ID,HL7.DocType,HL7.Envelope,HL7.Identifier,HL7.MessageTypeCategory,HL7.Name,HL7.OriginalDocId,HL7.ParentId, HL7.TimeCreated
FROM EnsLib_HL7.Message HL7
LEFT JOIN Ens.MessageHeader hdr
ON HL7.Id=hdr.MessageBodyId
WHERE hdr.MessageBodyId IS NULLI am trying to find the problem code that is causing the Orphaned messages, and an ACK showing up seems kind of Odd. While we do have Archive IO/Trace on, and Index NOT OK's set why would they show up as Orphaned messages?
Comments
I'm pretty sure it's because a message header isn't created for an ACK, since (in most cases) it's not sent anywhere. They're tracked in Ens_Util.IOLogObj, and cleaned up from there if selected in the Message Purge task.
Based on this, is it fair to assume that the following alteration to the SQL query in Scott's above post would give a truer reflection of orphaned messages?
SELECT HL7.ID,HL7.DocType,HL7.Envelope,HL7.Identifier,HL7.MessageTypeCategory,HL7.Name,HL7.OriginalDocId,HL7.ParentId, HL7.TimeCreated
FROM EnsLib_HL7.Message HL7
LEFT JOIN Ens.MessageHeader hdr
ON HL7.Id=hdr.MessageBodyId
LEFT JOIN Ens_Util.IOLogObj ack
ON HL7.Id = ack.InObjectId
WHERE hdr.MessageBodyId IS NULL AND ack.InObjectId IS NULLLooks right to me, @Julian.Matthews7786.
Thanks Jeffrey - it's certainly highlighted some hidden pockets of orphaned messages for me. I'm going to track this in a separate thread, but basically it seems there's a few edge cases where ACKs are being saved and don't end up in the IO log and don't get an entry in the Message Header table.