January 6, 2013 at 2:14 am
Hello,
I have the following stored procedure that runs as a job:
CREATE PROCEDURE [dbo].[proc_Master_Schedule]
AS
set XACT_ABORT ON
DECLARE @Count INT
DECLARE@LoopCount INT
DECLARE@ProgramID INT
DECLARE @strMessage NVARCHAR(MAX)
DECLARE @subject NVARCHAR(MAX)
SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram
FROM dbo.Schedule_Setup WITH (NOLOCK)
WHEREDeleted=0
SELECT @Count = @@RowCount
SET @LoopCount = 1
WHILE @LoopCount <= @Count
BEGIN
SELECT @ProgramID=ProgramID
FROM #tmpProgram
WHERE ID = @LoopCount
EXEC dbo.proc_Schedules @ProgramID
IF @@Error <> 0
SET @Message = 'The Schedule for Program ' + CONVERT(NVARCHAR,@ProgramID) + 'failed.'
exec Master.dbo.sp_sendSAM
@recipients='Dev.Team@test.com',
@subject='proc_Master_Schedule failure',
@sender_email = 'SQL2008@test.com',
@Message = @Message,
@format ='html'
END
SET @LoopCount=@LoopCount + 1
END
DROP TABLE #tmpProgram
I would like the job to continue to run when there is a failure in executing the stored procedure for any of the ProgramIDs and also send an email that it failed for that specific ProgramID. So should the procedure fail for one ProgramID, then skip to the next ProgramID and notify. Also I need to have better error handling.
Any help with regards to this matter will be greatly appreciated.
Thank you.
January 6, 2013 at 3:57 am
Remove set XACT_ABORT ON and implement TRY CATCH http://msdn.microsoft.com/en-us/library/ms175976%28v=sql.100%29.aspx, so this proc should looks like (I can't test it so first test it):
CREATE PROCEDURE [dbo].[proc_Master_Schedule]
AS
-- set XACT_ABORT ON --line to remove
DECLARE @Count INT
DECLARE@LoopCount INT
DECLARE@ProgramID INT
DECLARE @strMessage NVARCHAR(MAX)
DECLARE @subject NVARCHAR(MAX)
SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram
FROM dbo.Schedule_Setup WITH (NOLOCK)
WHEREDeleted=0
SELECT @Count = @@RowCount
SET @LoopCount = 1
WHILE @LoopCount <= @Count
BEGIN
SELECT @ProgramID=ProgramID
FROM #tmpProgram
WHERE ID = @LoopCount
--TRY CATCH block
BEGIN TRY
EXEC dbo.proc_Schedules @ProgramID
END TRY
BEGIN CATCH
SET @Message = 'The Schedule for Program ' + CONVERT(NVARCHAR,@ProgramID) + 'failed.'
exec Master.dbo.sp_sendSAM
@recipients='Dev.Team@test.com',
@subject='proc_Master_Schedule failure',
@sender_email = 'SQL2008@test.com',
@Message = @Message,
@format ='html'
END CATCH;
--end of TRY CATCH block
SET @LoopCount=@LoopCount + 1
END
DROP TABLE #tmpProgram
January 7, 2013 at 6:17 am
Thanks for your reply.
The TRY...CATCH block worked so when the stored procedure failed for one program then it skipped to the next one and sent the email. I also used the Error Functions from Try...Catch to print the error no, proc name and error message.
Once again thank you for your suggestion.
January 7, 2013 at 7:29 am
I'm glad I could help 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply