Alert_Blocking-FormatSessionInfo.sql

-- Populate SessionInfo column with HTML details for sending email
-- Since there's a bunch of logic here, code is more readable doing this separate than mashing it in with the rest of HTML email creation
UPDATE lb
SET SessionInfo = '<span style="font-weight:bold">Login = </span>' + LoginName + '<br>' +
                  CASE WHEN TransactionCnt <> 0 
                    THEN '<span style="font-weight:bold">Transaction Count = </span>' + CAST(TransactionCnt AS nvarchar(10)) + '<br>' 
                    ELSE ''
                  END +
                  CASE WHEN WaitResource <> ''
                    THEN '<span style="font-weight:bold">Wait Resource = </span>' + COALESCE(WaitDescription,WaitResource) + '<br>' 
                    ELSE ''
                  END +
                  '<span style="font-weight:bold">Host Name = </span>' + HostName + '<br>' +
                  CASE WHEN DbName <> ''
                    THEN '<span style="font-weight:bold">DbName = </span>' + DbName + '<br>' 
                    ELSE ''
                  END +
                  '<span style="font-weight:bold">Last Request = </span>' + CONVERT(varchar(20),LastRequestStart,20) + '<br>' +
                  '<span style="font-weight:bold">Program Name = </span>' + ProgramName + '<br>' 
FROM #LeadingBlocker lb;

UPDATE b
SET SessionInfo = '<span style="font-weight:bold">Login = </span>' + LoginName + '<br>' +
                  '<span style="font-weight:bold">Host Name = </span>' + HostName + '<br>' +
                  CASE WHEN TransactionCnt <> 0 
                    THEN '<span style="font-weight:bold">Transaction Count = </span>' + CAST(TransactionCnt AS nvarchar(10)) + '<br>' 
                    ELSE ''
                  END +
                  CASE WHEN WaitResource <> ''
                    THEN '<span style="font-weight:bold">Wait Resource = </span>' + COALESCE(WaitDescription,WaitResource) + '<br>' 
                    ELSE ''
                  END +
                  '<span style="font-weight:bold">DbName = </span>' + DbName + '<br>' +
                  '<span style="font-weight:bold">Last Request = </span>' + CONVERT(varchar(20),LastRequestStart,20) + '<br>' +
                  '<span style="font-weight:bold">Program Name = </span>' + ProgramName + '<br>'
FROM #Blocked b;