SQL Creating SQL Server Agent Job with scheduler

74 Views Asked by At

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:

  1. I'd like to run the job not daily but monthly on first Monday of the month
  2. 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()
}
0

There are 0 best solutions below