Analysing Workflows that Failed to Complete

There are a number of reasons why CRM workflow instances fail to complete.  It is important you regularly check for failed workflows to ensure the integrity of  your system. 

A failed workflow instance might be indicative of:

  • A workflow rule operating under a user context which has insufficient permissions assigned
  • A sloppily written workflow rule that in some scenarios attempts record updates to records that no longer exist
  • Environmental issues / timeouts / system errors
  • Missing email addresses

Each time a workflow rule is triggered a System Job is created.  These jobs carry a status and when a failure is experienced they are stamped with an error code, an error message and are set to a ‘Waiting’ status.

Here’s a useful bit of SQL I have been using to analyse a system’s failed workflow instances.  It provides a meaningful description for the error codes I have encountered.  I put this behind an Excel report so that I can pivot the results and monitor volumes.

select asyncoperation0.asyncoperationid as ‘asyncoperationid’, asyncoperation0.name as ‘name’,

asyncoperation0.regardingobjectidname as ‘regardingobjectidname’, asyncoperation0.operationtypename as ‘operationtypename’,

asyncoperation0.statuscodename as ‘statuscodename’, asyncoperation0.owneridname as ‘owneridname’,

convert(date,asyncoperation0.startedon,123) as ‘startedon’,

asyncoperation0.statecodename as ‘statecodename’, convert(date,

asyncoperation0.createdon,123) as ‘createdon’,

asyncoperation0.errorcode,

[message],

waitingreason = case                                         when asyncoperation0.errorcode = -2147204784 then ‘SQL error’

                                                                        when asyncoperation0.errorcode = -2147220946 then ‘Cannot updated closed record’

                                                                        when asyncoperation0.errorcode = -2147187962 then ‘CRM permissions issue’

                                                                        when asyncoperation0.errorcode = -2147218688 then ‘Object address not found or party maked as non-emailable’            

                                                                        when asyncoperation0.errorcode = -2147220969 then ‘Record to be changed no longer exists’           

                                                                        when asyncoperation0.errorcode = -2147204303 then ‘Maximum field length exceeded’

                                                                        when asyncoperation0.errorcode = -2147220970 then ‘Cannot access file – file in use’

                                                                        when asyncoperation0.errorcode = -2147201001 then ‘Request timed out’           

                                                                        when asyncoperation0.errorcode = -2147204718 then ‘Invalid email address for send email’                         

                                                                        else ‘<Unknown>’

                                                end

from FilteredAsyncOperation as asyncoperation0

where (asyncoperation0.recurrencestarttimeutc is null and asyncoperation0.statuscode = 10)

and asyncoperation0.errorcode is not null

/*and case                                            when asyncoperation0.errorcode = -2147204784 then ‘SQL error’

                                                                        when asyncoperation0.errorcode = -2147220946 then ‘Cannot updated closed record’

                                                                        when asyncoperation0.errorcode = -2147187962 then ‘CRM permissions issue’

                                                                        when asyncoperation0.errorcode = -2147218688 then ‘Object address not found or party maked as non-emailable’            

                                                                        when asyncoperation0.errorcode = -2147220969 then ‘Record to be changed no longer exists’           

                                                                        when asyncoperation0.errorcode = -2147204303 then ‘Maximum field length exceeded’

                                                                        when asyncoperation0.errorcode = -2147220970 then ‘Cannot access file – file in use’

                                                                        when asyncoperation0.errorcode = -2147201001 then ‘Request timed out’           

                                                                        when asyncoperation0.errorcode = -2147204718 then ‘Invalid email address for send email’                         

                                                                        else ‘<Unknown>’

                                                end = ‘<Unknown>’*/

order by asyncoperation0.startedon desc, asyncoperation0.asyncoperationid asc

Advertisements

One thought on “Analysing Workflows that Failed to Complete

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s