I'm creating a SQL Server Agent Job with a scheduler.
Please see the script down below. The script works properly, but I have two issues:
- I'd like to run the job not daily but monthly on first Monday of the month
- I get no errors creating the job, but the job doesn't run, when I edit the scheduler, I get the error: "you must specify the target servers on which this multi server job will execute".
I just want to execute this job on the local machine, adding or deleting the line:
$SQLJob.AddSharedSchedule($SQLJobSchedule.ID)
makes no difference, I get the same error. Thanks for you help.
$ServerList = Get-Content F:\Logs\ListOfServers.txt
foreach ($srv in $ServerList)
{
$SQLSvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($srv)
if ((Get-SQLAgentJob -ServerInstance $srv).Where{$_.Name -eq
'Delete_Users' -and $_.IsEnabled -eq $true})
{
Remove-DbaAgentJob -SqlInstance $srv -Job 'Delete_Users' -
Confirm:$false
}
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -
argumentlist $SQLSvr.JobServer, "Delete_Users"
$SQLJob.Create()
# step
$SQLJobStep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep
-argumentlist $SQLJob, "Step1_Delete_Users"
$SQLJobStep.Command = $Command
$SQLJobStep.DatabaseName = "master"
$SQLJobStep.Create()
# schedule
$SQLJobSchedule = New-Object -TypeName
Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $SQLJob,
"Delete_Users_Scheduler"
$SQLJobSchedule.FrequencyTypes = "Daily"
$SQLJobSchedule.FrequencyInterval = 1
$TimeSpan1 = New-TimeSpan -hours 15 -minutes 10
$SQLJobSchedule.ActiveStartTimeofDay = $TimeSpan1
$SQLJobSchedule.ActiveStartDate = get-date
$SQLJobSchedule.create()
$SQLJob.AddSharedSchedule($SQLJobSchedule.ID)
$SQLJob.Alter()
}