求获取启用跟踪选项的BizTalk Orchestration、Send Port等对象的查询方法

Got it, I totally get how tedious it would be to manually check tracking settings across all those BizTalk components—especially when you've got a ton of apps deployed. Below are targeted SQL queries you can run against your BizTalkMgmtDb to pull exactly the tracking status you need for each component type.

This query will show you which orchestrations have tracking enabled for properties, messages, and orchestration events:

SELECT
    a.nvcName AS ApplicationName,
    o.nvcName AS OrchestrationName,
    o.bTrackProperties AS TrackMessageProperties,
    o.bTrackMessageBeforeSend AS TrackMessageBeforeSend,
    o.bTrackMessageAfterReceive AS TrackMessageAfterReceive,
    o.bTrackOrchestrationEvents AS TrackOrchestrationEvents
FROM bts_orchestration o
JOIN bts_application a ON o.nApplicationID = a.nID
ORDER BY a.nvcName, o.nvcName;

• TrackMessageProperties: If enabled, tracks message context properties
• TrackMessageBeforeSend/AfterReceive: Tracks message bodies at those stages
• TrackOrchestrationEvents: Tracks orchestration lifecycle events (like start, end, shape execution)

Use this to get tracking status for all send ports, including whether they track properties or message bodies:

SELECT
    a.nvcName AS ApplicationName,
    sp.nvcName AS SendPortName,
    sp.bTrackProperties AS TrackMessageProperties,
    sp.bTrackMessageBeforeSend AS TrackMessageBeforeSend,
    sp.bTrackMessageAfterSend AS TrackMessageAfterSend,
    sp.bIsDynamic AS IsDynamicPort
FROM bts_sendport sp
JOIN bts_application a ON sp.nApplicationID = a.nID
ORDER BY a.nvcName, sp.nvcName;

• IsDynamicPort: Flags if this is a dynamic send port (useful for filtering if needed)
• The tracking fields correspond to whether message bodies/properties are tracked before sending out or after being processed

This query covers receive locations, showing tracking for messages pre/post-receive and property tracking:

SELECT
    a.nvcName AS ApplicationName,
    rp.nvcName AS ReceivePortName,
    rl.nvcName AS ReceiveLocationName,
    rl.bTrackProperties AS TrackMessageProperties,
    rl.bTrackMessageBeforeReceive AS TrackMessageBeforeReceive,
    rl.bTrackMessageAfterReceive AS TrackMessageAfterReceive
FROM bts_receivelocation rl
JOIN bts_receiveport rp ON rl.nReceivePortID = rp.nID
JOIN bts_application a ON rp.nApplicationID = a.nID
ORDER BY a.nvcName, rp.nvcName, rl.nvcName;

• Note: Receive locations are tied to receive ports, so we include both for full context

Pipelines have their own tracking configurations—this query pulls tracking settings for all deployed pipelines, plus the application they belong to:

SELECT
    a.nvcName AS ApplicationName,
    p.nvcName AS PipelineName,
    p.bTrackEvents AS TrackPipelineEvents,
    p.bTrackMessageParts AS TrackMessageParts,
    CASE p.nPipelineType
        WHEN 1 THEN 'Receive Pipeline'
        WHEN 2 THEN 'Send Pipeline'
        ELSE 'Unknown'
    END AS PipelineType
FROM bts_pipeline p
JOIN bts_application a ON p.nApplicationID = a.nID
ORDER BY a.nvcName, PipelineType, p.nvcName;

• TrackPipelineEvents: Tracks pipeline stage execution events
• TrackMessageParts: Tracks individual message parts processed by the pipeline

Quick Tips #

• Add a WHERE a.nvcName = 'YourTargetApplication' clause to any query to filter results to a specific app
• These queries work for BizTalk Server 2013 R2, 2016, and 2020 (older versions may have minor field name differences, but the core tables remain the same)
• Make sure you have read permissions on the BizTalkMgmtDb database before running these

内容的提问来源于stack exchange，提问作者Piotr Grudzień