As I was poking about trying to move a support utility forward- I noticed that one command in me “grunt” works console app made use of a Stored Procedure I buried in the BizTalk MSG DB. It’s not big thing. Until I realized that when our systems where upgraded and moved about, I didn’t move my magic SP forward… Well Duh! (I won’t go into how one should never really bolt on crap to a server product, but it was a useful tool)…
The SP was just complicated enough not to be able to just re-create from scratch – Yeah, one of those.
In a nutshell, the SP looks through active ports within a BizTalk system and does an active message count for each port that is processing a message (like I said- Not a trivial matter. Lucky, or was I foreseeing the future? I placed the SP code inside my console app- so as not to lose it….. Wow, I must be a “genus”.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Scott Edwards -- Create date: way back -- Description: Used by the integration tool to get port message counts. -- ============================================= CREATE PROCEDURE MHK_PortMSGcounts AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET DEADLOCK_PRIORITY LOW CREATE TABLE #Temp (MaxRetries int, Active int, SendPort nvarchar(256), IsPrimaryTransport int) declare @nvcAppName sysname declare MyCursor CURSOR FAST_FORWARD FOR SELECT nvcApplicationName FROM [BizTalkMsgboxDb]..[Applications] WITH (NOLOCK) open MyCursor FETCH NEXT FROM MyCursor INTO @nvcAppName WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #Temp exec (' SELECT MAX(q.nRetryCount) AS MaxRetries ,COUNT(*) AS Active<br> ,sp.nvcName AS SendHandlersHostServiceName ,spt.bIsPrimary AS IsPrimaryTransport FROM ' + @nvcAppName + 'Q as q WITH (NOLOCK) INNER LOOP JOIN Subscription as s WITH (NOLOCK) ON q.uidServiceID = s.uidServiceID AND s.uidPortID = q.uidPortID INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport] as sp WITH (NOLOCK) ON q.uidServiceID = sp.uidGUID INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport_transport] as spt WITH (NOLOCK) ON sp.nID = spt.nSendPortID AND spt.uidGUID = q.uidPortID GROUP BY sp.nvcName, s.uidPortID, spt.bIsPrimary ORDER BY SendHandlersHostServiceName ASC') FETCH NEXT FROM MyCursor INTO @nvcAppName END SELECT * FROM #Temp ORDER BY Active DESC close MyCursor deallocate MyCursor DROP TABLE #Temp END GO