functions/SetJobSchedules.ps1
|
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 [int[]] $ScheduleIdentities = @() try { $db = New-Object Microsoft.SqlServer.Management.Smo.Database $db = $SqlServer.Databases.Item("msdb") $ds = $db.ExecuteWithResults("select s.schedule_id 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) { $ScheduleIdentities += $row.schedule_id } } catch { throw $_.Exception } $msg = "Dropping all schedules for job $jobname" Write-Verbose $msg -Verbose foreach ($ScheduleIdentity in $ScheduleIdentities) { try { $db.ExecuteNonQuery("EXEC dbo.sp_delete_schedule @schedule_id = '" + $ScheduleIdentity + "', @force_delete = 1;") } catch { throw $_.Exception } } $schedules = $root.Schedules [System.Xml.XmlElement] $schedule = $null 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) $create = $true } 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 } } } |