CODEWITHSUNDEEP
c#sql-serversql-server-agent
user2007948
user2007948

Reputation: 19

Is there any way to update Sql Server Agent jobs schedule time by programmatically

presently we have many jobs are sheduling different time is there any way we can run all the jobs to schedule at same by programatically.

Upvotes: 1

Views: 8481

Answers (3)

Deadly-Bagel
Deadly-Bagel

Reputation: 1620

Full PowerShell script to change all jobs to run at 2:30am:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$svr = "."
$sql = new-object `
  Microsoft.SqlServer.Management.Smo.Server `
  $svr;;

$time = new-object -type "system.timespan" -argumentlist @(2,30,0)

$sql.jobserver.jobs | foreach-object {
    $_.jobschedules[0].activestarttimeofday = $time
    $_.jobschedules[0].alter()
}

$time can be tweaked as necessary, an IF statement can be added in the loop to only modify particular jobs, if the job has more than one schedule then it will need to be modified as the above only looks at the first schedule.

Upvotes: 0

jim31415
jim31415

Reputation: 8808

You can change the start time using T-SQL in the msdb..sysschedules table. Maybe you could create a stored procedure and call it from your C# code.

The snippet below will set all jobs to run at 6:30:45am. 

-- times are stored as integers, eg. 63045 = 06:30:45
update s set
    s.active_start_time = 63045
from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id

See dbo.sysschedules (Transact-SQL) for more info.

Upvotes: 3

Matt
Matt

Reputation: 2098

You should look into SMO. This class gives programmatic access to many of the things you can do in SQL Management Studio. Below is a link on how to start a job using SMO.

http://msdn.microsoft.com/en-us/library/ms202348.aspx

Upvotes: 1

Related Questions