How to Cancel All Orchestrator jobs with SQL

Published by Laurie Rhodes -

When automated systems go wrong you may need to cancel a backlog of pending or running Orchestrator jobs directly with SQL.

For reference, the StatusId field from Orchestrator’s Job table provides the current status of the job.  These numeric represent:

0 = Pending

1 = Running

2 = Failed

3 = Cancelled

4 = Completed

The script may be run directly with SQL Management Studio.

[[javascript]]
/****** Script for Cancelling en-masse Orchestrator Jobs  ******/
DECLARE              @JobId uniqueidentifier
 
DECLARE curs CURSOR FOR
 
SELECT  [Id]
  FROM [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs]
  WHERE [StatusId] = '1'
  ORDER BY [CreationTime] ;
 
OPEN curs
 
FETCH NEXT FROM curs
INTO @JobId
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
exec [Microsoft.SystemCenter.Orchestrator.Runtime].[CancelJob] @JobId ,  N'S-1-5-500'
 
FETCH NEXT FROM curs
    INTO @JobId
END 
 
CLOSE curs
DEALLOCATE curs
[[/javascript]]

 

Tags
Orchestrator