SourceField: Email html table from sql stored procedure:

Tuesday
Email html table from sql stored procedure:

        
CREATE PROCEDURE [dbo].[EmailTable]
/**********************************************************************************************************
--  DESCRIPTION: Email table 
--
--                DATE          AUTHOR                  TFS#        RELEASE
--      CREATED:  06/30/2025    
--     MODIFIED:  12/01/2025   
**********************************************************************************************************/
AS
BEGIN
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

if EXISTS (SELECT *
                FROM BizTalkMgmtDbR2.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
                INNER JOIN BizTalkMgmtDbR2.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
                ON RL.ReceivePortId = RP.nID
                INNER JOIN BizTalkMgmtDbR2.dbo.bts_application AS APP WITH(READPAST, ROWLOCK)
                ON RP.nApplicationID = APP.nID
                where APP.nvcName in ('biztailapp1','biztailapp2','BizTalkApp3')
                AND  Name Not in ('ReceivePortToIgnore1')
                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 BizTalkMgmtDbR2.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
        INNER JOIN BizTalkMgmtDbR2.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
            ON RL.ReceivePortId = RP.nID
        INNER JOIN BizTalkMgmtDbR2.dbo.bts_application AS APP WITH(READPAST, ROWLOCK)
            ON RP.nApplicationID = APP.nID
        where APP.nvcName in ('biztailapp1','biztailapp2','BizTalkApp3')
        AND  Name Not in ('ReceivePortToIgnore1')
            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='primary@email.com',
                    @copy_recipients ='secondary@email.com',
                    @subject =  'Biztalk recieve location suspended',
                    @body = @body,
                    @body_format = 'HTML' ;
    END
END
SourceField

Tuesday

Email html table from sql stored procedure:

No comments:

test smtp server with powershell

codestyle
