The following two queries return all send and receive ports/locations that use a given pipeline component.
You can filter on the friendly name or the fully qualified name. This query can be very useful when you need to change a pipeline component,
and you need to “roll off” the applications that use it, make the change, then “roll them back on.
use BizTalkMgmtDb SELECT app.nvcName AS [Application], ass.nvcName AS [Assembly], rp.nvcName AS [Receive Port], rl.Name AS [Receive Location], rppl.Name AS [Receive Pipeline], rppl.FullyQualifiedName as [RecievePipelineFQ] FROM bts_application app WITH(NOLOCK) LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON rp.nApplicationID = ass.nApplicationID LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id WHERE rppl.Name like '%shared%' and rppl.FullyQualifiedName like '%V2%' ORDER BY 1,2,3
SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
sppl.FullyQualifiedName as [Send PipelineFQ]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON sp.nApplicationID = ass.nApplicationID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
sppl.Name like ‘%shared%’
and sppl.FullyQualifiedName like ‘%V2%’
ORDER BY 1,2,3
You could make it fancier and come up with one query that does a union of the two, but then you probably would need to add another column to indicate if it’s a receive or send.
If you have orchestrations involved, there is another blog post that shows how to xref orchestrations to pipelines.