EDI Reporting and curious case of missing 997

We all agree that out of box EDI reporting in Biztalk leaves lot of gaps. For example, it is not easy to find all messages which were sent to our partners that didn't send 997 back to us. One can go on Biztalk console and click each message to find if Ack was received. But this is a very tedious approach and time consuming too. Instead, creating a query using BAM views can quickly give us desired results. This way a report can be set up using SSRS that can be viewed across the organization.

SELECT TOP (100) PERCENT InterchangeControlNo, ReceiverID, ReceiverQ, ApplicationReceiver, DATEADD(HOUR, - 5, TimeCreated) AS ProcessTime, TransactionSetId
FROM dbo.bam_TransactionSetActivity_AllInstances AS T
WHERE (SenderID LIKE ' [your org ID] %') AND (TransactionSetId <> '997') AND (NOT EXISTS
(SELECT RecordID, ActivityID, InterchangeActivityID, GroupControlNo, InterchangeControlNo, ReceiverID, SenderID, ReceiverQ, SenderQ, InterchangeDateTime,
Direction, AckProcessingStatus, AckStatusCode, DeliveredTSCount, AcceptedTSCount, AckIcn, AckIcnDate, AckIcnTime, ErrorCode1, ErrorCode2,
ErrorCode3, ErrorCode4, ErrorCode5, TimeCreated, RowFlags, LastModified
FROM dbo.bam_FunctionalAckActivity_AllInstances AS F
WHERE (GroupControlNo = T.GroupControlNo) AND (SenderID = T.ReceiverID)))
ORDER BY TimeCreated DESC

Remote Desktop issue with VM role

While trying to connect to Biztalk VM image in Azure, if the remote desktop does not connect, try one or all of the following things:

1. Reduce the size of VM image to small (smaller size and lesses processors)
2. Ensure endpoint exists or create one.
3. Delete the VM image and create a new one with a different geographical location. Try different locations within USA. (this did the trick for me)