Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.