BizTalk BRE SQL Script: Get rule set details by policy name

declare @vRuleSetID int
declare @publish int = 0
declare @policyName varchar(200)
set @policyName = '%Policy_ProductDetails_v1%'

SELECT @vRuleSetID  = nRuleSetID FROM [dbo].[re_ruleset]
where strName like @policyName


SELECT * FROM [dbo].[re_ruleset] where nRuleSetID = @vRuleSetID
SELECT * FROM [dbo].[re_tracking_id] where nRuleSetID = @vRuleSetID

Biztalk SQL Queries: Get List of receive locations , receive ports and host instance details by application name

select

   app.nvcName as ApplicationName,
   rp.nvcName,
   rl.Name,
   rl.InboundTransportURL
   ,host.Name

from bts_application as app

left join dbo.bts_receiveport as rp on rp.nApplicationID = app.nID
left join dbo.adm_ReceiveLocation as rl on rl.ReceivePortId = rp.nID

join [dbo].[adm_HostInstance] hi on hi.Id = rl.ReceiveHandlerId
join [dbo].[adm_Server2HostMapping]  hm on hi.Svr2HostMappingId =  hm.Id
join [dbo].[adm_Host] host  on hm.HostID = host.Id

where

app.nvcName like 'Application Name'


Biztalk SQL Queries: Get List of receive locations and receive ports details by application name

Biztalk SQL Queries: Get List of receive locations , receive ports and host instance details by application name

Biztalk SQL Queries: Get List of receive locations and receive ports details by application name

By Application Name:

select
   app.nvcName as ApplicationName,
   rp.nvcName,
   rl.Name,
   rl.InboundTransportURL

from bts_application as app

left join dbo.bts_receiveport as rp on rp.nApplicationID = app.nID
left join dbo.adm_ReceiveLocation as rl on rl.ReceivePortId = rp.nID
left join dbo.bts_receiveport_transform as rpt on rpt.nReceivePortID = rp.nID

where

app.nvcName like 'Application Name'


Biztalk SQL Queries: Get List of receive locations and receive ports details by application name