BizTalk: The almost lost secret sauce

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”.

[sql highlight=”5,6,7,8,9″]
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
[/sql]