Suspended Messages

by | Jul 25, 2009 | BizTalk Community Blogs via Syndication | 0 comments

Here is the stored procedure that needs to placed on the same server (but different database) as the MessageBoxDb. Then you simply have a job that runs this stored procedure (again, it never should need to be turned off)

This will check for new suspended messages every 30 seconds.

CREATE PROCEDURE [dbo].[MonitorBTSMessages] 
AS
BEGIN
    SET NOCOUNT ON;
    while(1=1)
    BEGIN
    WAITFOR DELAY '00:00:30'
        DECLARE @SUSPENDEDMESSAGES INT
        SELECT @SUSPENDEDMESSAGES=COUNT(*)
        FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
        WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
        IF (@SUSPENDEDMESSAGES>0)
        BEGIN
                declare @thisSubject varchar(100)
                select top 1 @thisSubject='Suspended message on '+  nvcErrorProcessingServer
                FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
                WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
                and nErrorCategory=0
                Declare @body1 varchar(8000)
                SELECT @body1 = ISNULL(@body1+'Adapter: '+nvcAdapter+' at '+nvcURI+'<br />Description: '+nvcErrorDescription+'<br /><br />','')
                 FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
                WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
                and nErrorCategory=0
                ORDER BY nvcErrorDescription
                EXEC msdb.dbo.sp_send_dbmail @recipients='support@company.com',
                @copy_recipients ='supervisor@company.com;manager@company.com',
                @subject = @thisSubject,
                @body = @body1,
                @body_format = 'HTML' ;
        END
    END
END

Submit a Comment