I have a strange issue with one of my workflows for which I can't find a solution. Basically, the workflow loops through a list of records and runs a SQL select query for each record. If the query returns a result, this data is written into a corresponding column for this record.
This flow is scheduled to run daily and send a notification to myself and one other user at completion. However, we are receiving multiple mails from this flow. The email contains a summary of the latest run like number of records processed and start and end time of the flow. Every mail contains a different end time. I also receive error notifications which correspond with these end times.
In the Nintex log there is only one error message: GetWOdata failed to start. However, the flow is still in the list of running workflows, but doesn't process any more records. The moment this happens is not consistent, sometimes it's after 80 records, sometimes 150 or even more. Total number of records varies but is around 2000-2500.
I've already tried adding a pause at the beginning of the flow and commiting changes every 100 records.
Anyone has any other ideas how to fix this?
Using Sharepoint On-prem and Nintex 2013
First off thanks for putting the SharePoint version.. Kudos on that.
You may be experiencing a timeout issue with the workflow engine executing the requested query and returning results. Have you tried batching the query and having a site workflow run against it like a scheduler? Ideally you would want to process say 100 records at a time, but it really depends on what you are actually doing in SQL as well. Something to consider.
Regarding the multiple emails, this could be in part by the workflow engine attempting to complete an set of tasks and getting queued by the timer job thus multiple notifications getting backed up for a single process.