I have come across a scenario where it required to update sql agent scheduler execution date time every month in a different date.
We can achieve this in couple of ways.
1. We can store the execution date time in a table and fetch that value and update it.
2. We can update the date time every month by running the script manually.
Please check below the execution script.
We need to update the MSDB.DBO.sysschedules table.
We can achieve this in couple of ways.
1. We can store the execution date time in a table and fetch that value and update it.
2. We can update the date time every month by running the script manually.
Please check below the execution script.
We need to update the MSDB.DBO.sysschedules table.
--You can use other columns as per your requirement
select name,enabled,active_start_date,active_end_date,date_modified
from MSDB.DBO.sysschedules\
Declare @dt_active_start datetime
set @dt_active_start=GETDATE() --Fetch the value from table if it stored
update MSDB.DBO.sysschedules
set active_start_date=CONVERT(char(8),@dt_active_start,112)
,enabled=1
,date_modified=GETDATE()
where name='Scheduler Name'
You can use other fields for updating as per the requirement..
No comments:
Post a Comment