SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error handling in while loop...skip if failure


Error handling in while loop...skip if failure

Author
Message
tudorn
tudorn
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 22
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)
WHERE Deleted=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.
e4d4
e4d4
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 2399
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)
WHERE Deleted=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


tudorn
tudorn
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 22
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.
e4d4
e4d4
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 2399
I'm glad I could help :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search