Start a SQL Agent Job Step via Powershell

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of M. Chalut M. Chalut 3 months, 4 weeks ago.

  • Author
    Posts
  • #49286
    Profile photo of M. Chalut
    M. Chalut
    Participant

    Hi,

    Can somebody please direct me to some documentation on how to start a SQL Agent Job via Powershell? I would like to start the job at step entered by user. If no step entered, then start at the first step. Is there something written on that?

    Thanks in advance for your help 🙂

    • This topic was modified 4 months ago by Profile photo of M. Chalut M. Chalut.
  • #49310
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You could use sp_start_job which has a parameter step_name and execute it as discussed in this forum:

    http://www.sqlservercentral.com/Forums/Topic868082-1351-1.aspx

    Having it use or not use the step would be simple logic:

    $jobName = "MySQLJob"
    $stepName = "My Step"
    
    if ($stepName) {
        $sqlCMD = "EXEC dbo.sp_start_job N'{0}', @step_name = N'{1}'" -f $jobName, $stepName
    }
    else {
        $sqlCMD = "EXEC dbo.sp_start_job N'{0}'" -f $jobName
    }
    
    $sqlCMD
    
    #or 
    
    $sqlCMD2 = "EXEC dbo.sp_start_job N'{0}'" -f $jobName
    
    if ($stepName) { $sqlCMD2 += ", @step_name = N'{0}'" -f $stepName }
    $sqlCMD2
    

    Output:

    EXEC dbo.sp_start_job N'MySQLJob', @step_name = N'My Step'
    EXEC dbo.sp_start_job N'MySQLJob', @step_name = N'My Step'
    

    Set the $stepName to $null and you get:

    EXEC dbo.sp_start_job N'MySQLJob'
    EXEC dbo.sp_start_job N'MySQLJob'
    
  • #49342
    Profile photo of M. Chalut
    M. Chalut
    Participant

    OK well I just came across some code that is actually working, if I am calling the SQL Agent job only. Doesn't look like your solution at all!!! But since the following is working for me, I would only like to add the JobStep portion...

     
    param ( [string]$ServerName,[string]$JobName,[string]$StepName )
    
    write-host "Starting SQL Agent Job $($JobName) on Server $($ServerName)"
    #write-host "JobName $($JobName)"
    $date=Get-Date
    write-host "It is now: $($date)"
    
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    
    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")
    $job = $srv.jobserver.jobs["$JobName"] 
    $step = $job.JobSteps["$StepName"] 
    
    $jobstart="No" 
    
    if (($job))
    {  
       $job.Start()
       $jobstart="Yes"
       Start-Sleep -s 5  # Pause for 5 seconds (optional) - was 30 seconds (v1); v2=5
    }
    else
       {
            $jobstart="Not found"
       }
    if ($jobstart -eq "Yes")
      { 
      write-host "Job $($JobName) on Server $($ServerName) started"
      $i=0
      do 
       { 
        $job.Refresh();   
        $iRem = $i % 5;
            $jobrunning=$job.CurrentRunStatus.ToString();
            if ($iRem -eq 0) 
               {
               $date=Get-Date
               write-host "Job $($JobName) Processing--Run Step:$($job.CurrentRunStep) Status:$($job.CurrentRunStatus.ToString())... at $($date)"
               } 
    
        Start-Sleep -s 10;  # Pause for 10 seconds  - was 60 seconds (v1); v2=10
            $i++;
            #if ($i -gt 8)
            #  {
            #   write-host Job $($JobName) is $($job.CurrentRunStatus.ToString())
            # }
      }
      while ($job.CurrentRunStatus.ToString() -ne "Idle") 
      if ($job.LastRunOutcome -ne "Cancelled")
        {write-host "Job Processing done"}
      else
        {write-host "Job Processing cancelled/aborted"}  
       #   $jobrunning="TRUE" 
           write-host "$($srv.name) $($job.name)"
               write-host "Last job outcome $($job.LastRunOutcome)"
               write-host "Last job outcome $($job.LastRunDate)"
               $l = $job.LastRunDate
               $p=$job.LastRunOutcome
               $q=$job.LastRunOutcome
               $x = $job.EnumHistory()
               $y = $job.EnumHistory().Rows
    
               if ($job.EnumHistory().Rows[0] -ne $null)
                {
                    write-host "xxxx $($job.EnumHistory().Rows[0].Message)"
                    }
                    if ($job.EnumHistory().Rows[1] -ne $null)
                      {
                       write-host "yyyyy $($job.EnumHistory().Rows[1].Message)"
                      }
                      $z = $job.JobSteps.Count - 1
                      for ($i=0;$i -le $z;$i++)
                       {
                        $m = $job.JobSteps[$i].LastRunDate
                            write-host "Name: $($job.JobSteps[$i].Name) RunDate: $($job.JobSteps[$i].LastRunDate) Status: $($job.JobSteps[$i].LastRunOutCome)"
    
                         if ($l -gt $m) 
                              {
                                $p="FailedOrAborted"
                              }
                       } 
    
      if ($p -eq "Failed")
        {
            write-host "Job returned with Failed status"
              exit 2
        } 
    
      if ($p -ne "FailedOrAborted")
        {
            if ($p -ne "Cancelled")
            {exit 0}
            else
            {
              write-host "Job Cancelled xxxxx"
              exit 3
            }
            }
      else 
        {
            write-host "Job Failed or Aborted"
            exit 2
            }
      }
      else
      {
      write-host "Unable to Start Job $($JobName) on Server $($ServerName)"
      write-host "Reason: Job may not exist or not enabled."
      exit 1
      }
    
    
  • #49400
    Profile photo of M. Chalut
    M. Chalut
    Participant

    Found the solution...

    param( 
    	[string]$ServerName,
    	[string]$JobName,
    	[string]$StepName 
    )
    
    Write-Verbose "Starting SQL Agent Job $($JobName) on Server $($ServerName)"
    
    $date = Get-Date
    Write-Verbose "It is now: $($date)"
    
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    
    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")
    $job = $srv.jobserver.jobs["$JobName"] 
    
    if ($job)
    {	
    	if($StepName -ne '') 
    	{
    		$job.Start($StepName)
    	}
    	else 
    	{
    		$job.Start()
    	}
    
    	Write-Verbose "Job $($JobName) on Server $($ServerName) started"
    	$i = 0
    }
    
  • #49583
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Glad you found a solution. Only thing I see to update is line 19, it should be simply checking if the value is not null:

    if ($StepName) {
    ...
    

    If you are worried about it being empty, then you should use Advanced Function Parameters and use [ValidateNotNull()] or another parameter validation to ensure you are not getting anything unexpected.

  • #49725
    Profile photo of M. Chalut
    M. Chalut
    Participant

    Thank you Rob!!! I will look into it!
    I am all new to this, so I need all the tips I can get.
    Have a nice day 🙂

You must be logged in to reply to this topic.