Wednesday, April 22, 2020

SQL SERVER DDL Job Configuration

DDL_Daily_Report is a job with 2 steps
Step 1: Run
EXEC [MaintenanceDB].[dbo].[spDDLLog_Info_Email]
----------------
Step 2: Failure

declare @Job_ID uniqueidentifier;
select @Job_ID = job_id from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID));
exec usp_GetJobFailureData_Main @Job_ID, 'Alerts@myDomain.com', 'help@myDomain.com', 'HTML'

/*-------------------------------------------------------------------------------------------*/


BEGIN TRY
EXEC myDatabase.dbo.ProcessingSwitchJob
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage varchar(max) = = 'Exception occurred in proc myDatabase.dbo.ProcessingSwitchJob. Exception detail: '

  SELECT @ErrorMessage = @ErrorMessage + ''+ ERROR_MESSAGE() + '';

  EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMail',
  @recipients='systems@domain.com',
  @subject='Processing Switch Exception',
  @body=@ErrorMessage,
  @importance='High'
            
        RETURN;
END CATCH


/*-------------------------------------------------------------------------------------------*/

Declare @ErrorMessage varchar(500);

BEGIN CATCH

  exec msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMail'
  ,@recipients='systems@domain.com'
  ,@subject='ERROR PROD01 job: Processing Load Step 2 Switch Fail'
  ,@body='Scheduled Processing Load !  Please check system.'
  
  SELECT @ErrorMessage=ERROR_MESSAGE() ;
  RAISERROR( @ErrorMessage,16,1 );
END CATCH

SPECIAL NOTES: Please set the following appropriately so that the job logs the error and then continues to the next step.
on job success, goto next step
on job failure, goto next step

No comments:

Post a Comment