InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 02:55:07 GMT20http://www.sqlservercentral.com/Forums/Topic1403316-1292-1.aspxI'm glad I could help :-)Mon, 07 Jan 2013 07:29:44 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1403316-1292-1.aspxThanks 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.Mon, 07 Jan 2013 06:17:21 GMTtudornhttp://www.sqlservercentral.com/Forums/Topic1403316-1292-1.aspxRemove [b]set XACT_ABORT ON[/b] and implement [b]TRY CATCH[/b] [url]http://msdn.microsoft.com/en-us/library/ms175976%28v=sql.100%29.aspx[/url], so this proc should looks like (I can't test it so first test it):[code="sql"]CREATE PROCEDURE [dbo].[proc_Master_Schedule] AS -- set XACT_ABORT ON --line to removeDECLARE @Count INTDECLARE @LoopCount INTDECLARE @ProgramID INTDECLARE @strMessage NVARCHAR(MAX)DECLARE @subject NVARCHAR(MAX)SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram FROM dbo.Schedule_Setup WITH (NOLOCK) WHERE Deleted=0SELECT @Count = @@RowCountSET @LoopCount = 1WHILE @LoopCount <= @CountBEGIN 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 + 1ENDDROP TABLE #tmpProgram[/code]Sun, 06 Jan 2013 03:57:41 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1403316-1292-1.aspxHello,I have the following stored procedure that runs as a job: [code="sql"]CREATE PROCEDURE [dbo].[proc_Master_Schedule] AS set XACT_ABORT ONDECLARE @Count INTDECLARE @LoopCount INTDECLARE @ProgramID INTDECLARE @strMessage NVARCHAR(MAX)DECLARE @subject NVARCHAR(MAX)SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram FROM dbo.Schedule_Setup WITH (NOLOCK) WHERE Deleted=0SELECT @Count = @@RowCountSET @LoopCount = 1WHILE @LoopCount <= @CountBEGIN 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 + 1ENDDROP TABLE #tmpProgram[/code]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.Sun, 06 Jan 2013 02:14:06 GMTtudorn