Author Posts

August 4, 2016 at 2:40 pm

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 2 years, 1 month ago by  M. Chalut.

August 4, 2016 at 4:44 pm

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'

August 4, 2016 at 6:54 pm

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
  }

August 5, 2016 at 12:18 pm

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
}

August 8, 2016 at 5:25 pm

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.

August 9, 2016 at 2:36 pm

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 🙂

July 24, 2018 at 7:32 pm

The excellent dbatools PowerShell module includes the Start-DbaAgentJob cmdlet, which does exactly this. If you don't already know about dbatools, you're welcome 🙂