Biztalk: How to receive email notification when biztalk receive location is suspended

Following stored procedure has been created:
       
IF EXISTS (SELECT '*' FROM sysobjects WHERE id = object_id(N'[dbo].[BiztalkRecieveLocationNotification]'))
DROP PROCEDURE [dbo].[BiztalkRecieveLocationNotification]
GO

CREATE PROCEDURE [dbo].[BiztalkRecieveLocationNotification]
/**********************************************************************************************************
--  DESCRIPTION: Email list of suspended receive locations 
--
--                DATE          AUTHOR                  
--      CREATED:  
--     MODIFIED:  
--                                                                                                       
**********************************************************************************************************/
AS
BEGIN

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


if EXISTS (SELECT *
                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 ='MyBiztalkApp'  AND
                Disabled=-1
            )
BEGIN

     SET @xml = CAST((
    SELECT
         APP.nvcName AS 'td',''
        ,RP.nvcName AS 'td',''
        ,RL.InboundTransportURL AS 'td',''
        ,RL.Name AS 'td',''
    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 ='MyBiztalkApp'  AND
        Disabled=-1
     FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)
      )


    /* Body as the HTML table */
            SET @body ='<html><body><H3>Biztalk Suspended Receive locations:</H3>
                        <table border = "2" align="center"  > 
                        <tr>
                        <th> Application Name </th> <th> Port Name </th> <th> Adapter URI </th><th> Location Name </th></tr>'

            SET @body = @body + @xml +'</table></body></html>'

    /*Execute the Stored Proc to send us the Mail using the Parameters */
                    EXEC msdb.dbo.sp_send_dbmail @recipients='my@email.com',
                    @copy_recipients ='my@email.com',
                    @subject =  'Biztalk recieve location suspended',
                    @body = @body,
                    @body_format = 'HTML' ;
    END

END
Biztalk

Wednesday, July 1, 2015

How to receive email notification when biztalk receive location is suspended

No comments:

Post a Comment

counter
