locked
Message Context properties in Biztalk database Tables RRS feed

  • Question

  • Hello,

    I want to retrieve messase, and status of msg and receive file Name using stored procedure

    can anybody knows where these  Message Context properties got stored in BizTalk databases.

    Thanks.


    • Edited by _NiLeSh Thursday, March 12, 2015 7:18 AM
    Thursday, March 12, 2015 7:18 AM

Answers

All replies

  • I assume you are talking about where these are stored in the Tracking Database, since messages (body and context) only resides in the Message Box for a short period.

    In the Tracking Database  (BizTalkDTADb), Context is stored in dbo.Tracking_Parts1 table in the imgPropBag column.

    Note: Only if body tracking is enabled on the Port(s) will the body and context be stored.

    However the context is encrypted and can only be viewed using the BizTalk API, the Console or 3rd party tools:

    Easy BizTalk Tracking

    Morten la Cour

    Thursday, March 12, 2015 8:23 AM
  • Could you let us know your requirement in a bit detail, I believe then we should be able to help you better.

    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    Thursday, March 12, 2015 1:11 PM
  • Why are you trying to do this?

    Querying the MessageBox directly is not a supportable or recommendable operation.  There are specific API's for this in WMI and ExploerOM.

    Querying the Tracking database is a different story.  That schema is pretty easy to figure out.

    Thursday, March 12, 2015 10:38 PM
    Moderator
  • Select ins.uidInstanceID AS 'InstanceID',
    m.nvcName AS 'ApplicationName',
    ins.nvcErrorDescription AS 'ErrorDescription', ins.dtCreated AS 'CreationTime',
    ins.dtSuspendTimeStamp AS 'SuspendedDateTime',
    'ReceiveFileName' AS 'ReceiveFileName' 
    from InstancesSuspended ins 
    
    
    left outer join Instances i ON ins.uidInstanceID=i.uidInstanceID 
    
    left outer JOIN Applications a ON 
    a.uidAppID = i.uidAppOwnerID 
    
    left outer JOIN ServiceClasses sc ON
     sc.uidServiceClassID = i.uidClassID 
    
    left outer JOIN Services s ON 
    s.uidServiceID = i.uidServiceID 
    
    INNER JOIN Modules m ON
    m.nModuleID = s.nModuleID 
    
    where 
    i.nvcErrorID = '0xC0C01B4e' or 
    i.nvcErrorID ='0xc0c016a2'

    Hi prashant I am writing one stored procedure which will give me above columns for routing failure message

    I have achieved it almost but I am unable to get Receive File Name using this query.

    Please share if any Idea.

    Thanks Prashant

    Tuesday, March 17, 2015 2:01 PM
  • Because the Receive File Name only resides in the Context Properties!

    It wouldn't make sense to have a direct column named something with "File name", since only some Adapters (File, FTP, POP3 to some extent) operates with the concept.

    Morten la Cour

    Tuesday, March 17, 2015 2:09 PM
  • I'll just warn again that querying the MessageBox like this is not supportable and this query has the potential for some undesirable side-effects.

    The correct way to accomplish what you appear to be trying is through WMI and there are many examples of prebuild queries that give you exactly what you need, including the Message Context.

    Here are some examples:

    http://blogs.msdn.com/b/biztalknotes/archive/2013/05/22/saving-suspended-messages-using-wmi-powershell-and-the-microsoft-biztalk-operations-assembly-part-i.aspx

    http://blogs.msdn.com/b/biztalknotes/archive/2013/10/02/saving-suspended-messages-using-wmi-powershell-and-the-microsoft-biztalk-operations-assembly-part-ii.aspx

    You can also create a listener for Suspended Message event using WMI:

    https://msdn.microsoft.com/en-us/library/aa577357.aspx?f=255&MSPPError=-2147217396

    Basically, do not try to query the MessageBox directly.

    • Marked as answer by Angie Xu Friday, March 20, 2015 1:47 AM
    Tuesday, March 17, 2015 2:33 PM
    Moderator