Apr 262012

Have you ever needed to query through the BizTalk Management Console and find some messages that are tracked, but the console keeps timing out?

Here is a query that you can run:

use [BizTalkDTADb] GO set DEADLOCK_PRIORITY LOW SELECT TOP 1000 ms.strStatus AS [Event Type], svcs.strServiceName AS [Service Name], tr.strAdapter AS [Adapter], mioe.strUrl AS [URI], sn.strSchemaName AS [Schema Name], po.strPortName AS [Port Name], DATEADD(MI, DATEDIFF(MI,GETUTCDATE(),GETDATE()), mioe.dtTimestamp) AS [Timestamp], mioe.nPartCount AS [Part Count], mioe.nMessageSize AS [Size], ds.strDecryptionSubject AS [Decryption Certificate], ss.strSigningSubject AS [Signature], pn.strPartyName AS [Party Name], mioe.uidActivityId AS [Activity ID], mioe.uidMessageInstanceId AS [Message Instance ID], mioe.uidServiceInstanceId AS [Service Instance ID], si.uidServiceClassId AS [Service Class ID], svcs.strServiceType AS [Service Class] FROM dta_SchemaName sn JOIN [dbo].[dta_MessageInOutEvents] mioe ON mioe.nSchemaId = sn.nSchemaId LEFT JOIN [dbo].[dta_Adapter] tr ON tr.nAdapterId = mioe.nAdapterId LEFT JOIN [dbo].[dta_MessageStatus] ms ON mioe.nStatus = ms.nMessageStatusId LEFT JOIN [dbo].[dta_DecryptionSubject] ds ON ds.nDecryptionSubjectId = mioe.nDecryptionSubjectId LEFT JOIN [dbo].[dta_SigningSubject] ss ON ss.nSigningSubjectId = mioe.nSigningSubjectId LEFT JOIN [dbo].[dta_PartyName] pn ON pn.nPartyId = mioe.nPartyId LEFT JOIN [dbo].[dta_ServiceInstances] si ON mioe.uidServiceInstanceId = si.uidServiceInstanceId AND mioe.uidActivityId = si.uidActivityId LEFT JOIN [dbo].[dta_Services] svcs ON si.uidServiceId = svcs.uidServiceId LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = mioe.nPortId --Put the port name that you want to look at here: WHERE CAST(po.strPortName AS nvarchar(4000)) = 'Meditech Port' ORDER BY mioe.dtTimestamp DESC

Did I tell you that you should not be doing this? YOU SHOULDN’T!!!!!

Sorry, the comment form is closed at this time.