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

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