June 10, 2015 at 4:39 am
Hi everyone,
I am working on of the T-sql statement that do updates. This statement is running in the job. We set up the notification reached to operator when the job failed.
But I need help whenever the transactions are rolled back, it has to notify to the team. Below are the steps in the job
DECLARE @NextRunDate DATETIME = DATEADD(hh,2,CAST(CAST(DATEADD(day,1,GETUTCDATE()) as DATE) as DATETIME))
BEGIN TRY
BEGIN TRANSACTION
UPDATE [RECompanyTask]
SET NextRunDate = @NextRunDate
WHERE SetupOptions = 0 AND [Enabled] = 1
UPDATE [REExecutionSettings]
SET NextRunDate = @NextRunDate, ExecutionStatus = 3
WHERE SetupOptions = 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
If @@TRANCOUNT >0
ROLLBACK TRANSACTION
END CATCH
June 10, 2015 at 5:24 am
Add something into the catch block to send mail, or after the rollback add detail to a logging table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2015 at 6:01 am
You could RAISERROR after the rollback is complete so that your alert fires.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2015 at 6:31 am
Grant Fritchey (6/10/2015)
You could RAISERROR after the rollback is complete so that your alert fires.
Either one of these will get you what you're after. It just depends on which way you prefer and what message you want to receive.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply