Skip to main content
Planview Customer Success Center

Prevent request workflow from cancelling sporadically, and how to detect the source of the problem?

Question:
When a request is created, a custom stored procedure copies a value from the parent project into a request UDF. Sometimes the value is not copied into the UDF for various reasons and the request workflow cancels with no indication of an error. Resetting the workflow process instance or the workflow state instance does not solve the problem.
How could this be prevented, and how could the source of the problem be identified?


Answer:
For identifying the custom procedure(s) that failed for a specific request, the request profile page can be used:

1- Pick the last workflow process the request was in, from the workflow process list; all process steps will appear as cancelled.
2- Starting from the first step in the process, right-click, go to History tab and scroll down, look for an error.
3- The step with the error is the one that caused the workflow process to cancel.
4- Check this step in workflow definition (System Manager) to identify the stored procedure responsible for the failure.

Custom stored procedures may fail with various errors, if the data model is not examined carefully in advance. E.g. a typical error message is "Cannot insert the value NULL into column 'UpdatedBy', table 'Changepoint.dbo.UDFCode'; column does not allow nulls. INSERT fails."

Take the following aspects into account when writing a custom workflow stored procedure:

- Check for primary key constraints on tables you are inserting into; e.g. attempt to insert without checking for an existing record may cause primary key errors

- Check for field properties on tables/fields you are inserting into; e.g. if a field does not allow NULL values, consider using ISNULL() function to assign a default value in such cases

- Resetting the workflow does not revert the changes done by custom stored procedures; e.g. it will not delete records inserted into UDFCode table. Resetting the workflow means going through the same steps, executing custom procedures again, therefore attempting to delete/insert/update again. Consider the possibility of a workflow reset when designing a custom workflow stored procedure, so that the recurring changes done by the custom code do not cause further problems.