Share via

BizTalk Server: View Send Port Subscriptions

  • Article

Introduction

In a BizTalk Group you can in the Group View query all the subscriptions. However, you will not see the exact filter setting for the subscription unless you double click a subscription and click the Expression tab.

http://i208.photobucket.com/albums/bb152/Steef-Jan/TSQL_zpsa7w6gfz5.png

Picture 1. Send Port Subscription details.

In case you have multiple subscription with send ports in an application than you have to go through all off them to get an good view and understanding of them. An alternative could be a TSQL script to execute against the BizTalk Management Database.

TSQL Script

A TSQL, found in the MSDN Code Gallery named Show subscriptions for a BizTalk application, will provide means to determine the subscription(s) of all the send ports for one- or all BizTalk applications within a BizTalk group: 

USE BizTalkMgmtDb
GO
 
WITH
TmpXMLNode
   ( SendPortName
     , ApplicationName
     , tmpcol
   )
AS
   (SELECT
    SP.nvcName AS  SendPortName
     , APP.nvcName AS  ApplicationName
     , CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), SP.nvcFilter),'>','>'),'<','<'),
       'xmlns="http://www.w3.org/2001/XMLSchema-instance"','') AS  XML) AS  tmpcol
   FROM
     bts_sendport AS  SP
     INNER JOIN bts_application AS  APP
       ON SP.nApplicationID = APP.nID
   WHERE
    CONVERT(VARCHAR(MAX), nvcFilter) <> ''
   )
 
--All BizTalk applications in a group
SELECT
  SendPortName
   , ApplicationName
   , CONVERT(VARCHAR(255), nref.query('data(@Property)')) AS  FilterProperty
   , CONVERT(VARCHAR(255), nref.query('data(@Value)')) AS  FilterValue
   , CONVERT(VARCHAR(255), nref.query('data(@Operator)')) As  Operator
FROM
  TmpXMLNode
   CROSS APPLY
     TmpXMLNode.tmpcol.nodes('/Filter/Group/Statement') AS  R(nref)
--One application
WHERE
 ApplicationName = '<Your Application Name>'

Other languages

This article is also available in the following languages:

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.