I’m adding to my collection of SQL queries that run against the BizTalk database.

The first query below goes from Orchestration to Send Port. If the orchestration uses Direct Binding, it will have a logical Orchestration PortName, but no bindings to a SendPort.

The second query goes from SendPort to Orchestration.

I’d love to show the OperationName, but that most likely just in the code, not the BizTalk Database. It often gives you the method name or the SQL Stored Proc name, depending on how the person coded the orchestration. A SendPort can have several operations in the XML, and that is available. The orchestration can have multiple SEND shapes tied to the same Logical Send Port. So BizTalk only needs to bind each logical port to a physical send port, and at run time it passes the operation name.

The queries are slightly different in the order of the joins. The Case logic turns the code numbers into more useful text strings.

Link to Summary of All BizTalk SQL Database Commands on this blog site.

Orchestrations to SendPorts

use BizTalkMgmtDB
select 
 o.nvcName as 'Orchestration',
 sp.nvcName as 'SendPort',
 sp.bTwoWay as 'TwoWay',
 op.nvcName as 'OrchPortName', 
-- op.nvcName as'Operation',
 op.nBindingOption, 
  CASE 
    WHEN op.nBindingOption=1 THEN 'Specify-Later' 
    WHEN op.nBindingOption=2 THEN 'Specify-Now?' 
    WHEN op.nBindingOption=3 THEN 'Direct' 
    WHEN op.nBindingOption=4 THEN 'Dynamic?' 
    ELSE ad.Name 
 END as 'BindingOptionTxt',
 sp.nvcDescription, 
 sp.DateModified,
 sp.bStopSendingOnFailure as 'StopSendingOnFailure?',
 sp.bDynamic as 'IsDynamic',
 pipe.Name as 'Pipeline',
 sp.nvcFilter as 'Filter', 
 spt.nvcAddress as 'Address_URL',
 --pto.nvcName as 'PortTypeOperation',
  CASE 
    WHEN spt.nvcAddress like 'mssql:%' THEN Concat(ad.Name,':SQL')
  WHEN ad.Name like 'WCF-WSHttp' THEN Concat(ad.Name,':REST')
    ELSE ad.Name 
 END as 'Adapter' 
 from bts_orchestration o -- on o.nID = op.nOrchestrationID  
 left outer join bts_orchestration_port op on  o.nID = op.nOrchestrationID 
 left outer join bts_orchestration_port_binding opb on opb.nOrcPortID = op.nPortTypeID
 left outer join bts_sendPort sp on sp.nID = opb.nSendPortID 
 left outer join bts_pipeline pipe on pipe.Id = sp.nSendPipelineId 
 left outer join bts_sendport_transport spt on spt.nSendPortId = sp.nID and spt.nTransportTypeId is not null 
 left outer join adm_adapter ad on ad.Id = spt.nTransportTypeId
  where o.nvcName like '%YourOrchName%'

SendPort to Orchestrations

use BizTalkMgmtDB
 select 
 sp.nvcName as 'SendPort',
 sp.bTwoWay as 'TwoWay',
 sp.DateModified,
 sp.bStopSendingOnFailure as 'StopSendingOnFailure?',
 sp.bDynamic as 'IsDynamic',
 op.nBindingOption, 
  CASE 
    WHEN op.nBindingOption=1 THEN 'Specify-Later' 
    WHEN op.nBindingOption=2 THEN 'Specify-Now?' 
    WHEN op.nBindingOption=3 THEN 'Direct' 
    WHEN op.nBindingOption=4 THEN 'Dynamic?' 
    ELSE ad.Name 
 END as 'BindingOptionTxt', sp.nvcDescription, 
 o.nvcName as 'Orchestration',
 op.nvcName as'OrchLogicalPortName',
 --op.nvcName as'Operation',
 pipe.Name as 'Pipeline',
 sp.nvcFilter as 'Filter', 
 spt.nvcAddress as 'Address_URL',
 --pto.nvcName as 'PortTypeOperation',
  CASE 
    WHEN spt.nvcAddress like 'mssql:%' THEN Concat(ad.Name,':SQL')
  WHEN ad.Name like 'WCF-WSHttp' THEN Concat(ad.Name,':REST')
    ELSE ad.Name 
 END as 'Adapter' 
 from bts_sendport sp 
 inner join bts_orchestration_port_binding opb  on sp.nID = opb.nSendPortID 
 inner join bts_orchestration_port op on op.nID = opb.nOrcPortId 
 inner join bts_orchestration o on o.nID = op.nOrchestrationID  
 inner join bts_pipeline pipe on pipe.Id = sp.nSendPipelineId 
 inner join bts_sendport_transport spt on spt.nSendPortId = sp.nID and spt.nTransportTypeId is not null 
 inner join adm_adapter ad on ad.Id = spt.nTransportTypeId
 where s.nvcName = 'YourSendPortName'