Useful T-SQL queries on BizTalk Filter, Send Port, Receive Location, etc.

[2011-02-17]

Disable all receive locations of Schedule adapter if the system has any suspended messages

UPDATE RL
SET RL.[Disabled] = -1
FROM BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
      INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
            ON RL.ReceivePortId = RP.nID
      INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK)
            ON RP.nApplicationID = APP.nID
      INNER JOIN BizTalkMgmtDb.dbo.adm_Adapter AS AD WITH(READPAST, ROWLOCK)
            ON RL.AdapterId = AD.Id
WHERE
      APP.nvcName = '?'
      AND AD.[Name] = 'Schedule'
      AND RL.Name NOT LIKE '%Disabled%'
      AND RL.[Disabled] = 0
      AND EXISTS(SELECT * FROM BizTalkMsgBoxDb.dbo.InstancesSuspended WITH(READPAST, ROWLOCK))

 

 

Looking for down Receive Locations and send ports

SELECT
    SPTP.nvcAddress AS SendingURI
    , SP.nvcName AS SendPortName
FROM
    BizTalkMgmtDb.dbo.bts_sendport_transport AS SPTP WITH(READPAST, ROWLOCK)
    INNER JOIN BizTalkMgmtDb.dbo.bts_sendport AS SP WITH(READPAST, ROWLOCK)
        ON SPTP.nSendPortID = SP.nID
    INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK)
        ON SP.nApplicationID = APP.nID
WHERE
    APP.nvcName = '?'
    AND SP.nPortStatus <> 3
    AND SP.nvcName NOT LIKE '%unenlisted%'
    AND SPTP.nTransportTypeId IS NOT NULL

 

 

SELECT
    RL.Name AS ReceiveLocationName
    , RL.InboundTransportURL AS InboundTransportURL
    , RP.nvcName AS ReceivePortName
FROM BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
    INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
        ON RL.ReceivePortId = RP.nID
    INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK)
        ON RP.nApplicationID = APP.nID
WHERE
    APP.nvcName = '?'
    AND RL.[Disabled] = -1
    AND RL.Name NOT LIKE '%Disabled%'

 

 

 

[2010-01-21]
Looking for all send ports and their applications that subscribe to a receive port's messages
 

WITH
TmpXMLNode
  ( SendPortName
    , ApplicationName
    , tmpcol
  )
AS
  (SELECT
    SP
.nvcName AS SendPortName
    , APP.nvcName AS ApplicationName
    , CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), SP.nvcFilter),'&gt;','>'),'&lt;','<'),
      '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) <> ''
  )

SELECT
  SendPortName
  , ApplicationName
  , CONVERT(VARCHAR(255), nref.query('data(@Value)')) AS FilterValue
  , CONVERT(VARCHAR(255), nref.query('data(@Property)')) AS FilterProperty
FROM
  TmpXMLNode
  CROSS APPLY
    TmpXMLNode.tmpcol.nodes('/Filter/Group/Statement') AS R(nref)
WHERE
  CONVERT(VARCHAR(255), nref.query('data(@Property)')) = 'BTS.ReceivePortName' -- filter type
  AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) = '[Receive port name]'



 

 

[2010-01-20]
Looking for all the send ports that send to a specific location:

SELECT
      SPTP.nvcAddress AS SendingURI
      , SP.nvcName AS SendPortName
      , SP.nPortStatus AS SendPortStatus
      , APP.nvcName AS ApplicationName
      , APP.DateModified AS ApplicationDateModified
FROM
      bts_sendport_transport AS SPTP
      INNER JOIN bts_sendport AS SP
            ON SPTP.nSendPortID = SP.nID
      INNER JOIN bts_application AS APP
            ON SP.nApplicationID = APP.nID
WHERE
      SPTP.nvcAddress like 'ftp://ftp.[some company].com%'

 

Print | posted on Wednesday, January 20, 2010 11:33 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski