Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
199 lines (197 sloc) 9.39 KB
Function Set-JobSchedules {
<#
.Synopsis
Create or modify SQL Agent Job Schedules.
.Description
SQL Agent Job Schedule will be created or updated to match the settings in the xml file.
.Parameter sqlServer
The SQL Connection that SQL Agent Job Schedule is on/will be created on.
.Parameter root
The XML Object
.Example
$SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;"
$JobManifestXmlFile = "C:\Reports\Our_First_Job.xml"
$SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString
[xml] $_xml = [xml] (Get-Content -Path $JobManifestXmlFile)
$x = Get-Xml -XmlFile $_xml
$sqlAgentJob = Set-Job -SqlServer $SqlConnection -root $x
Set-JobSchedules -SqlServer $SqlConnection -root $x -job $SqlAgentJob
Disconnect-SqlConnection -SqlDisconnect $SqlConnection
#>
[CmdletBinding()]
param
(
[Microsoft.SqlServer.Management.Smo.SqlSmoObject]
[ValidateNotNullorEmpty()]
$SqlServer,
[System.Xml.XmlLinkedNode]
[ValidateNotNullorEmpty()]
$root,
[Microsoft.SqlServer.Management.Smo.Agent.AgentObjectBase]
[ValidateNotNullorEmpty()]
$job
)
[string]$JobName = $Job.Name
$schedules = $root.Schedules
[System.Xml.XmlElement] $schedule = $null
$domain = [Environment]::UserDomainName
$uname = [Environment]::UserName
[string]$whoAmI = "$domain\$uname"
$ServerResults = @{}
try {
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $SqlServer.Databases.Item("msdb")
$ds = $db.ExecuteWithResults("select s.schedule_id, s.name from sysjobs j
inner join sysjobschedules js on js.job_id = j.job_id
inner join sysschedules s on s.schedule_id = js.schedule_id
where j.name = '" + $jobName + "'")
$t = $ds.Tables[0]
Foreach ($row in $t.Rows) {
$ServerResults.Add($row.name, $row.schedule_id)
}
}
catch {
throw $_.Exception
}
if ($ServerResults.Count -gt 0) {
$msg = "Dropping all schedules for job $jobName that do not exist in XML..."
Write-Verbose $msg -Verbose
$ds = $db.ExecuteWithResults("SELECT IS_SRVROLEMEMBER('sysadmin') as 'AmISysAdmin';")
$AmISysAdmin = $ds.Tables[0].Rows[0]."AmISysAdmin"
if ($AmISysAdmin -eq 1) {
Write-Verbose "User $whoAmI is sysadmin on instance, so job schedule can be dropped irrespetive of owner." -Verbose
}
if ($AmISysAdmin -eq 0){
Write-Verbose "User $whoAmI not sysadmin, so need to check that they are owner of job schedules, otherwise schedules not owned by user cannot be dropped."
$ds = $db.ExecuteWithResults("SELECT SUSER_SID() AS SID;")
[String]$CurrentUserSid = $ds.Tables[0].Rows[0]."SID"
}
foreach ($ServerSchedule in $ServerResults.Keys) {
if ($schedules.schedule.name -notcontains $ServerSchedule) {
try {
if ($AmISysAdmin -eq 0)
{
$ds = $db.ExecuteWithResults("select owner_sid from sysschedules syssch where syssch.name = '$ServerSchedule'")
[string]$JobScheduleOwnerSid = $ds.Tables[0].Rows[0]."owner_sid"
if(@(Compare-Object $CurrentUserSid $JobScheduleOwnerSid -SyncWindow 0).Length -gt 0)
{
Write-Error "User $whoAmI is not owner of Schedule $ServerSchedule. Either alter or set user executing PowerShell to sysadmin!"
Throw
}
}
Write-Verbose "SQL Statement executed to drop schedule:" -Verbose
Write-Verbose "EXEC dbo.sp_delete_schedule @schedule_id = '$($ServerResults.Get_Item($ServerSchedule)) ',@force_delete = 1;" -Verbose
$db.ExecuteNonQuery("EXEC dbo.sp_delete_schedule
@schedule_id = '" + $($ServerResults.Get_Item($ServerSchedule)) + "',
@force_delete = 1;")
$msg = "Schedule $($ServerSchedule) on job $jobName deleted..."
Write-Verbose $msg -Verbose
}
catch {
throw $_.Exception
}
}
}
}
foreach ($schedule in $schedules.ChildNodes) {
#name of schedule
[string]$schedule_name = $schedule.Name
#schedule child nodes
if ($schedule.Enabled) {
[bool]$schedule_enabled = if ($schedule.Enabled -eq "True") {$True} else {$false}
}
else {
[bool]$schedule_enabled = $false
}
[string]$Schedule_startDateString = $schedule.StartDate
[string]$Schedule_EndDateString = $schedule.EndDate
#frequency child nodes
[string[]]$schedule_FrequencyInterval = $schedule.Frequency.Interval
[string]$schedule_FrequencyRecurrs = $schedule.Frequency.Recurrs
[string]$schedule_frequencyType = $schedule.Frequency.Type
#daily frequency child nodes
[string]$Schedule_DailyFrequencyEvery = $schedule.DailyFrequency.Every
[string]$Schedule_DailyFrequencyInterval = $schedule.DailyFrequency.Interval
[string]$Schedule_DailyFrequencyStartTimeHour = $schedule.DailyFrequency.StartHour
[string]$Schedule_DailyFrequencyStartTimeMinute = $schedule.DailyFrequency.StartMinute
[string]$Schedule_DailyFrequencyStartTimeSecond = $schedule.DailyFrequency.StartSecond
[string]$Schedule_DailyFrequencyEndTimeHour = $schedule.DailyFrequency.EndHour
[string]$Schedule_DailyFrequencyEndTimeMinute = $schedule.DailyFrequency.EndMinute
[string]$Schedule_DailyFrequencyEndTimeSecond = $schedule.DailyFrequency.EndSecond
try {
$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($job, $schedule_name)
if ($ServerResults.Keys -notcontains $schedule_name){
$create = $true
}
else {
$create = $false
$js = $job.JobSchedules | Where-Object {$_.Name -eq $schedule_name}
$js.Refresh()
}
}
catch {
throw $_.Exception
}
#formatting frequency type or assigning it a numeric value if it is something like "Monday, Wednesday" or "Weekday".
if (![String]::IsNullOrWhiteSpace($schedule_FrequencyInterval)) {
if ($schedule_frequencyType -eq "Weekly") {
[int]$FrequencyInterVal = Get-FrequencyIntervalValue $schedule_FrequencyInterval
}
else {
[int]$FrequencyInterVal = [convert]::ToInt32($schedule_FrequencyInterval, 10)
}
}
try {
$js.IsEnabled = $schedule_enabled
#"if" statements are used to verify string is not empty in those cases where an enpty string will cause a "create job schedule" failure.
if (![string]::IsNullOrEmpty($Schedule_startDateString)) {
$js.ActiveStartDate = [DateTime]$Schedule_startDateString
}
if (![String]::IsNullOrEmpty($Schedule_EndDateString)) {
$js.ActiveEndDate = [DateTime]$Schedule_EndDateString
}
if (![String]::IsNullOrEmpty($FrequencyInterVal)) {
$js.FrequencyInterval = $FrequencyInterVal
}
if (![String]::IsNullOrEmpty($schedule_FrequencyRecurrs)) {
$js.FrequencyRecurrenceFactor = $schedule_FrequencyRecurrs
}
if (![String]::IsNullOrEmpty($schedule_frequencyType)) {
$js.FrequencyTypes = $schedule_frequencyType
}
if (![String]::IsNullOrEmpty($Schedule_DailyFrequencyEvery)) {
$js.FrequencySubDayTypes = $Schedule_DailyFrequencyEvery
}
if (![String]::IsNullOrEmpty($Schedule_DailyFrequencyInterval)) {
$js.FrequencySubDayInterval = [convert]::ToInt32($Schedule_DailyFrequencyInterval, 10)
}
if (![String]::IsNullOrEmpty($Schedule_DailyFrequencyStartTimeHour)) {
$StartTimeSpan = New-TimeSpan -Hours ([convert]::ToInt32($Schedule_DailyFrequencyStartTimeHour , 10)) -Minutes ([convert]::ToInt32($Schedule_DailyFrequencyStartTimeMinute , 10)) -Seconds ([convert]::ToInt32($Schedule_DailyFrequencyStartTimeSecond , 10))
$js.ActiveStartTimeOfDay = $StartTimeSpan
}
if (![string]::IsNullOrEmpty($Schedule_DailyFrequencyEndTimeHour)) {
$EndTimeSpan = New-TimeSpan -Hours ([convert]::ToInt32($Schedule_DailyFrequencyEndTimeHour , 10)) -Minutes ([convert]::ToInt32($Schedule_DailyFrequencyEndTimeMinute , 10)) -Seconds ([convert]::ToInt32($Schedule_DailyFrequencyEndTimeSecond , 10))
$js.ActiveEndTimeOfDay = $EndTimeSpan
}
if ($create) {
try {
$js.Create()
Write-Verbose "Job Schedule $schedule_name created successfully." -Verbose
$create = $false
Remove-Variable -Name js
}
catch {
throw $_.Exception
}
}
else {
$js.Alter()
Remove-Variable -Name js
Write-Verbose "Job Schedule $schedule_name properties updated successfully." -Verbose
}
}
catch {
throw $_.Exception
}
}
}