Bhubaneswar, Odisha, India
+91-8328865778
support@softchief.com

SQL Query for System JOB CRM

SQL Query for System JOB CRM

Hello Friend, this article will explain about the system jobs ( Workflows, Dialogs, Imports etc.) in a details view using SQL query.

database-planning

The asyncoperation table stores details of system jobs (including asyn plugins). The following table details some of the different job types that are stored in the table.

The below query can be used to analysis system jobs of CRM in SQL server.

SELECT asyncoperationid — Primary Key of the async job record
,name — Name of the async job
,statecodename — State of the async job record
,statuscodename — Status of the async job record
,createdbyname — Created by user name
,createdon — Created on (local date time) of workflow  
,createdonutc — Created on (UTC date time) of workflow
,modifiedbyname — Modified by User name
,modifiedon — Modified on date time (Local)
,modifiedonutc — Modified on date time (UTC)
,startedon — The datetime the job started (Local)
,startedonutc — The datetime the job started (UTC)
,completedon — The datetime the job completed (Local)
,completedonutc — The datetime the job started (UTC)
,depth — This is for async plugins defines the depth of the async plugin
,errorcode — Error code of the failed job
,executiontimespan — The time taken for the execution
,friendlymessage — Message of the failed job
,message — Details message of the failed job
,messagename — Event Name
,operationtypename — Operation Type
,primaryentity = (select top 1 name from entity where objecttypecode= primaryentitytype) — Entity Name
,regardingobjectidname — Regarding object
,owneridname — Owner of the job
,regardingobjectentity = (select top 1 name from entity where objecttypecode= regardingobjecttypecode) —Regarding entity
FROM FilteredAsyncOperation

The below table defines the operation types and status code

operationtype Description
1 System Event
9 Collect SQM data
10* Workflow Operation
12 Update Match Code
25 Generate Full Text Catalog
27 Update Contract States
State Code Status Code
3 Completed 30 Succeded
31 Failed
32 Canceled
2 Locked 20 In Progress
21 Pausing
22 Canceling
1 Suspended 10 Waiting
0 Ready 0 Waiting for Resources

For removing workflow expansion tasks

http://support.microsoft.com/kb/957871/

For removing completed workflow entries
http://support.microsoft.com/kb/968755/