Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

Microsoft CRM System Job Views Explained

Microsoft CRM System Job Views Explained

  • Comments 1

Have you ever wondered what the different views under the System Job entity actually meant?  If so, keep reading..... 

First off, each System Job record relates to a record in the AsyncOperationBase table.  The AsyncOperationBase table is made up of a number of columns that serve different purposes.  For this blog, I'm only going to only cover a few columns:

  • AsyncOperationId
    • This is the unique value assigned to each and every record in the AsyncOperationBase table. All System Job records will relate to an entry in the AsyncOperationBase table with a specific AsyncOperationId.
  • OperationType
    • Each record will have an OperationType value. The OperationType column tells you what type of Async record you're dealing with. For example, all workflows have an OperationType of 10 and all duplicate detection MatchCode records have a value of 12. You can find the full list within the CRM SDK by searching for "AsyncOperationType Class (CrmHelpers)".
  • StateCode
    • The StateCode tells you what the overall state of the record is. Each state can have multiple status' (or StatusCode). The valid Async StateCode's are:
      • Completed = 3
        • Indicates that the asynchronous operation has executed to completion.
      • Locked = 2
        • Indicates that an instance of the asynchronous service has locked the operation so that no other instance can access it.
      • Ready = 0
        • Indicates that the asynchronous operation is ready to be executed.
      • Suspended = 1
        • Indicates that the asynchronous operation is temporarily suspended. For example, this could mean that a workflow is currently in a waiting state. As soon as the wait condition occurs, the workflow can continue.
  • StatusCode
    • The StatusCode gives you a bit more information relating to the StateCode. Each StatusCode relates to a specific Status Reason.
      • Completed (3)
        • Succeeded = 30
        • Failed = 31
        • Canceled = 32
      • Locked (2)
        • In Progress = 20
        • Pausing = 21
        • Cancelling = 22
      • Ready (0)
        • Waiting for Resources = 0
      • Suspended (1)
        • Waiting = 10

 

All System Jobs

  • This view returns all system jobs with any valid StateCode and a DeletionStateCode of 0:
    • A DeletionStateCode of 0 means that the record is not marked for deletion. A DeletionStateCode of 2 means that the record is marked for deletion.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@DeletionStateCode0 int',@DeletionStateCode0=0

 

Completed System Jobs

  • This view returns all system jobs with a completed state where the StateCode = 3 and the DeletionStateCode = 0.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.StateCode = @StateCode0 and asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@StateCode0 int,@DeletionStateCode0 int',@StateCode0=3,@DeletionStateCode0=0

 

Locked System Jobs

  • This view returns all system jobs in a locked state where the StateCode = 2 and the DeletionStateCode = 0.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.StateCode = @StateCode0 and asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@StateCode0 int,@DeletionStateCode0 int',@StateCode0=2,@DeletionStateCode0=0

 

My System Jobs

  • This view returns all system jobs that a particular user owns where the OwnerId = USER'S ID and DeletionStateCode = 0.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'' from AsyncOperation as asyncoperation0 where (asyncoperation0.OwnerId = @OwnerId0 and asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@OwnerId0 uniqueidentifier,@DeletionStateCode0 int',@OwnerId0='User's Id',@DeletionStateCode0=0

 

Ready System Jobs

  • This view returns all system jobs in a ready state where the StateCode = 0 and the DeletionStateCode = 0.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.StateCode = @StateCode0 and asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@StateCode0 int,@DeletionStateCode0 int',@StateCode0=0,@DeletionStateCode0=0

 

 Recurring System Jobs

  • This view returns all recurring system jobs where the RecurrenceStartTime is not null and the DeletionStateCode = 0.
  • The resulting query would be:

exec sp_executesql N'select top 51 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.RecurrenceStartTime as ''recurrencestarttime'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.RecurrenceStartTime is not null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.RecurrenceStartTime desc, asyncoperation0.AsyncOperationId asc',N'@DeletionStateCode0 int',@DeletionStateCode0=0

  

Suspended System Jobs

  • This view returns all system jobs in a suspended state where the StateCode = 1 and the DeletionStateCode = 0.
  • The most common reason for a record to be in a Suspended state would be due to the record being in a waiting state. As mentioned above, this could mean that a workflow is currently in a waiting state. As soon as the wait condition occurs, the workflow can continue.
  • The resulting query would be:

exec sp_executesql N'select top 200 asyncoperation0.AsyncOperationId as ''asyncoperationid'', asyncoperation0.Name as ''name'', asyncoperation0.RegardingObjectId as ''regardingobjectid'', asyncoperation0.OperationType as ''operationtype'', asyncoperation0.StatusCode as ''statuscode'', asyncoperation0.OwnerId as ''ownerid'', asyncoperation0.StartedOn as ''startedon'', asyncoperation0.StateCode as ''statecode'', asyncoperation0.PostponeUntil as ''postponeuntil'', asyncoperation0.RegardingObjectIdDsc as ''regardingobjectiddsc'', asyncoperation0.RegardingObjectIdName as ''regardingobjectidname'', asyncoperation0.RegardingObjectTypeCode as ''regardingobjecttypecode'', asyncoperation0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', asyncoperation0.OwnerIdType as ''owneridtype'', asyncoperation0.OwnerIdYomiName as ''owneridyominame'', asyncoperation0.OwnerIdDsc as ''owneriddsc'', asyncoperation0.OwnerIdName as ''owneridname'' from AsyncOperation as asyncoperation0 where (asyncoperation0.StateCode = @StateCode0 and asyncoperation0.RecurrenceStartTime is null and (asyncoperation0.DeletionStateCode in (@DeletionStateCode0))) order by asyncoperation0.StartedOn desc, asyncoperation0.AsyncOperationId asc',N'@StateCode0 int,@DeletionStateCode0 int',@StateCode0=1,@DeletionStateCode0=0


You can get more information about the StateCode and StatusCode (which translates to the Status Reason) columns by looking through the Microsoft CRM SDK (search for AsyncOperationState Enumeration).  The CRM SDK can be found on the www.microsoft.com/downloads site by searching for "crm sdk".


- Jon

  • Hi Jon,

    Thank you for this great little article. It really helped. I have massive queue of suspended jobs that I need to clear. With this i can go directly in to DB and do this.

    Thank you for your post.

    K

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post