I have a stored procedure that executes synchronously with PostJobTransactions, what it's intended to do is do a dirty read of tables jobt_cls_mst and bgtaskhistory_mst to retrieve any errors that are produced by PostJobTransactions. However, when my notification SP Notification_PostJobTransactionsSp fires, it appears to lock PostJobTransactions in a "hung" state. Can I please get some advice on my script? Thank you in advance.
ALTER PROCEDURE [dbo].[Notification_PostJobTransactionsSp]
(
@sendemail TINYINT = 0 OUTPUT, -- 1 = email sent/needed, 0 = no
@infobar InfobarType = NULL OUTPUT -- HTML body content
)
AS
BEGIN
SET NOCOUNT ON;
-- Non-blocking reads (dirty reads possible)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
DECLARE @MaxJobRows INT = 3;
-- Default: assume no email unless we find relevant rows
SET @sendemail = 0;
SET @infobar = NULL;
DECLARE @Now DATETIME2 (0) = SYSDATETIME();
DECLARE @HeaderTS NVARCHAR (30) =
CONVERT(NVARCHAR(10), CAST(@Now AS DATETIME ), 103) + N' ' +
CONVERT(NVARCHAR(8), CAST(@Now AS DATETIME ), 108);
DECLARE @BgErr TABLE
(
TaskNumber NVARCHAR(50) NULL,
TaskName NVARCHAR(255) NULL,
RecordDate DATETIME NULL,
CompletionStatus NVARCHAR(50) NULL,
TaskErrorMsg NVARCHAR(MAX) NULL
);
INSERT INTO @BgErr (TaskNumber, TaskName, RecordDate, CompletionStatus, TaskErrorMsg)
SELECT TOP (1)
CAST(TaskNumber AS NVARCHAR(50)),
CAST(TaskName AS NVARCHAR(255)),
RecordDate,
CAST(CompletionStatus AS NVARCHAR(50)),
CAST(TaskErrorMsg AS NVARCHAR(MAX))
FROM dbo.bgtaskhistory_mst WITH (NOLOCK)
WHERE taskname = 'PostJobTransactions'
AND TaskErrorMsg IS NOT NULL
ORDER BY RecordDate DESC;
DECLARE @BgErrCount INT = (SELECT COUNT(1) FROM @BgErr );
DECLARE @JobErrs TABLE
(
Job NVARCHAR(50) NULL,
jt_trans_num NVARCHAR(50) NULL,
m_trans_num NVARCHAR(50) NULL,
ErrorMsg NVARCHAR(MAX) NULL,
user_code NVARCHAR(50) NULL,
RecordDate DATETIME NULL
);
INSERT INTO @JobErrs (Job, jt_trans_num, m_trans_num, ErrorMsg, user_code, RecordDate)
SELECT TOP (@MaxJobRows )
CAST(jtm.Job AS NVARCHAR(50)),
CAST(jt.jt_trans_num AS NVARCHAR(50)),
CAST(jt.m_trans_num AS NVARCHAR(50)),
CAST(jt.ErrorMsg AS NVARCHAR(MAX)),
CAST(jt.user_code AS NVARCHAR(50)),
jt.RecordDate
FROM dbo.jobt_cls_mst jt WITH (NOLOCK)
LEFT JOIN dbo.jobtran_mst jtm WITH (NOLOCK)
ON jtm.trans_num = jt.jt_trans_num
WHERE jt.ErrorMsg IS NOT NULL
ORDER BY jt.RecordDate DESC;
DECLARE @JobErrCount INT = (SELECT COUNT(1) FROM @JobErrs );
IF (@BgErrCount = 0 AND @JobErrCount = 0)
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN;
END
SET @sendemail = 1;
DECLARE @Style NVARCHAR (MAX);
DECLARE @body NVARCHAR (MAX);
SET @Style =
N'<style type="text/css">' +
N' body { font-family: Arial, sans-serif; font-size: 14px; color: #333; }' +
N' h2 { margin: 18px 0 8px 0; }' +
N' table { border-collapse: collapse; width: 100%; margin-bottom: 18px; }' +
N' th { padding: 8px; border: 1px solid #ddd; background-color: #0078d4; color: #fff; text-align: left; }' +
N' td { padding: 8px; border: 1px solid #ddd; vertical-align: top; }' +
N' tr:nth-child(even) td { background-color: #f9f9f9; }' +
N'</style>';
SET @body = N'<html><head>' + @Style + N'</head><body>';
IF (@BgErrCount > 0)
BEGIN
SET @body +=
N'<h2>Post Job Transaction Background Task Errors: ' + @HeaderTS + N'</h2>' +
N'<table><thead><tr>' +
N'<th>TaskNumber</th>' +
N'<th>TaskName</th>' +
N'<th>Date</th>' +
N'<th>Status</th>' +
N'<th>Error Message</th>' +
N'</tr></thead><tbody>';
SET @body += CAST((
SELECT
td = ISNULL(TaskNumber, N''), '',
td = ISNULL(TaskName, N''), '',
td = ISNULL(
CONVERT(NVARCHAR(10), CAST(RecordDate AS DATETIME), 103) + N' ' +
CONVERT(NVARCHAR(8), CAST(RecordDate AS DATETIME), 108),
N''
), '',
td = ISNULL(CompletionStatus, N''), '',
td = ISNULL(TaskErrorMsg, N''), ''
FROM @BgErr
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX));
SET @body += N'</tbody></table>';
END
IF (@JobErrCount > 0)
BEGIN
SET @body +=
N'<h2>Post Job Transaction Errors: ' + @HeaderTS + N'</h2>' +
N'<table><thead><tr>' +
N'<th>Job No.</th>' +
N'<th>Job Transaction No.</th>' +
N'<th>Material Transaction No.</th>' +
N'<th>Error Message</th>' +
N'<th>User Initials</th>' +
N'<th>Date</th>' +
N'</tr></thead><tbody>';
SET @body += CAST((
SELECT
td = ISNULL(LTRIM(RTRIM(Job)), N''), '',
td = ISNULL(jt_trans_num, N''), '',
td = ISNULL(m_trans_num, N''), '',
td = ISNULL(ErrorMsg, N''), '',
td = ISNULL(user_code, N''), '',
td = ISNULL(
CONVERT(NVARCHAR(10), CAST(RecordDate AS DATETIME), 103) + N' ' +
CONVERT(NVARCHAR(8), CAST(RecordDate AS DATETIME), 108),
N''
), ''
FROM @JobErrs
ORDER BY RecordDate DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX));
SET @body += N'</tbody></table>';
END
SET @body += N'</body></html>';
SET @infobar = @body ;
DECLARE
@p _profile_name NVARCHAR(256),
@p _recipients NVARCHAR(MAX),
@p _subject NVARCHAR(MAX);
SELECT @p _profile_name = uf_dbmail
FROM dbo.parms_mst WITH (NOLOCK);
SELECT TOP (1) @p _recipients = uf_OracleCoErrorEmail
FROM dbo.parms_mst WITH (NOLOCK);
-- Subject: count of rows included (BG error row + job rows returned)
SET @p _subject = N'Post Job Transactions Errors: ' + CAST((@BgErrCount + @JobErrCount ) AS NVARCHAR(20));
IF COALESCE(LTRIM(RTRIM(@p _recipients)), N'') <> N''
BEGIN
-- Do NOT wait on msdb locks (prevents “hung” background task)
SET LOCK_TIMEOUT 0;
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@profile _name = @p _profile_name,
@recipients = @p _recipients,
@body = @infobar ,
@body _format = 'HTML',
@subject = @p _subject;
END TRY
BEGIN CATCH
END CATCH
SET LOCK_TIMEOUT -1;
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END TRY
BEGIN CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Conservative: do not block completion on notification failures
SET @sendemail = 0;
SET @infobar = NULL;
RETURN;
END CATCH
END
GO
