• BizTalk

    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]