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.
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/
Don’t miss the chance to participate in the upcoming Internship Program which will be done using Microsoft Dot Net Web Development Full Stack Technology. The new batch will be starting from May 20, 2024. We will have most experienced trainers for you to successfully complete the internship with live project experience.
Why to choose Our Internship Program?
Industry-Relevant Projects
Tailored Assignments: We offer projects that align with your academic background and career aspirations.
Real-World Challenges: Tackle industry-specific problems and contribute to meaningful projects that make a difference.
Professional Mentorship
Guidance from Experts: Benefit from one-on-one mentorship from seasoned professionals in your field.
Career Development Workshops: Participate in workshops that focus on resume building, interview skills, and career planning.
Networking Opportunities
Connect with Industry Leaders: Build relationships with professionals and expand your professional network.
Peer Interaction: Collaborate with fellow interns and exchange ideas, fostering a supportive and collaborative environment.
Skill Enhancement
Hands-On Experience: Gain practical skills and learn new technologies through project-based learning.
Soft Skills Development: Enhance communication, teamwork, and problem-solving skills essential for career success.
Free Demo Class Available
