[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),'>','>'),'<','<'),
'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%'