Thursday, February 2, 2012

SQL Agent: Script to disable and enable SQL agent jobs

The following script disables the job scedules in the agent
BEGIN
TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 0END TRY


BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 0END TRY

BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Disable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 

--select @rcEnd
The following script enables the job scedules in the agent

BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 1END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Enable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 


--select @rcEnd  
exec @rc = msdb.dbo.sp_send_dbmail
END CATCH
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 1END TRY
END CATCH
exec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 1END TRY
exec @rc = msdb.dbo.sp_send_dbmail
 
 
 
END CATCH
exec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 0END TRY

No comments:

Post a Comment