Updating workflow status to cancelled in the MS Dynamics CRM AsyncOperationBase table with a SQL stored procedure, status not always persisting

0
votes

I am currently diagnosing an issue with an implementation of MS Dynamics CRM 2013, whereby system jobs in CRM are programmatically cancelled soon after they have been created, if certain conditions in the application are met. However, these workflows have NOT cancelled, and continue processing - and I am wondering if this is due to the use of a SQL stored procedure to update the CRM database.

Here is the current (simplified) process:

  • Event in application triggers a CRM workflow, using the MS XRM OrganizationServiceProxy methods.
  • Something occurs in the application whereby we want to cancel the workflow that has recently been requested to start.
  • The "cancel workflow" request is saved, and picked up and processed by a custom application to process shortly after.
  • This application runs a stored procedure that finds the relevent AsyncOperation entry, and sets the ModifiedOn to getdate(), the StatusCode to 32 (Cancelled) and the StateCode to 3 (Completed).

Some time after succeeding to process the cancellation (as audited by our application), the workflow sometimes will continue to process, and the StatusCode/StateCode will no longer be 32 and 3. The modified DateTime in the AsyncOperation table will also be later than the application's "cancel workflow request processed" DateTime.

I've tried checking if the "StartWorkflow" request hasn't been processed by our application yet - but in every case, it has been sent to CRM before we attempt to stop it. So, CRM knows about the workflow, but updating the AsyncOperation table is not causing it to cancel. In many cases, the CreatedOn and/or ModifiedOn Datetime is AFTER we have attempted to stop the workflow, however, despite the start workflow request going to CRM before.

My main question is - is there a difference between sending a request to the OrganizationServiceProxy update AsyncOperation using:

workflow.Attributes["statecode"] = new OptionSetValue(3);
workflow.Attributes["statuscode"] = new OptionSetValue(32);
organizationServiceProxy.Update(workflow);

...versus a stored procedure that updates StateCode and StatusCode directly? I've read that it's usually a bad idea to update the CRM DB directly, but surely by retrieving asyncoperation from the organization service and then telling it to update specific columns, all it is doing is what the stored procedure would do? This is a CRM system with a high number of workflows being triggered and a lot of load, so I have a theory that it may be down to a workflow that's been triggered to start in CRM (but in a queued state) will NOT be in the AsyncOperation table until CRM picks it up... Which may explain that some of the entries have a "CreatedOn" date that is slightly later than the processed date of the request to STOP it. However, I'm not sure where workflows are stored in the DB when they are in a queued state, or if even they don't go to the AsyncOperation table instantly. I'm wondering if sending the request to CRM where it will probably run the same SQL that I am is a viable solution, or if anyone is aware of other elements that might cause a CRM workflow to continue to process and overwrite the StateCode/StatusCode despite having the statecode/statuscode set to cancelled/completed?

Due to load in production, any case where I can reduce abstraction and just directly affect the DB is not just welcome, but almost a necessity, due to timeouts and slow performance in CRM.

I see about 50 examples of this daily in production, so it's not isolated, but I cannot reproduce it in test environments... Which makes me believe moreso that it's a load issue, but whether CRM has additional steps when it updates a Statecode/Status code in the AsyncOperation table is my main concern. Thanks!

1
dynamics-crmcrmdynamics-crm-2013

1 Answers

0
votes

My main question is - is there a difference between sending a request to the OrganizationServiceProxy update AsyncOperation using:

workflow.Attributes["statecode"] = new OptionSetValue(3);
workflow.Attributes["statuscode"] = new OptionSetValue(32);
organizationServiceProxy.Update(workflow);

...versus a stored procedure that updates StateCode and StatusCode directly?

There is a huge difference. Except for a very few exceptions such as creating custom indexes, reading from the Filtered Views, and adjusting certain settings, doing anything directly the Dynamics SQL database is unsupported, especially writing data directly to tables.

Outside of the very limited exceptions, everything MUST be done through the API (i.e. OrganizationServiceProxy which implements IOrganizationService). CRM is a message-based platform. If you write directly to SQL you circumvent its entire messaging system, with unpredictable and unsupported results.

In your scenario there is likely other stuff going on that your SQL stored procedure is unaware of. In short, you should implement whatever you're trying to achieve via the API, and get rid of all SQL writes to the CRM database.

Due to load in production, any case where I can reduce abstraction and just directly affect the DB is not just welcome, but almost a necessity, due to timeouts and slow performance in CRM.

While it is tempting to want to operate directly on the database, think of CRM as an API rather than a database.

With that in mind, you can work on performance analysis and optimization.

For starters, it might be helpful to analyze slow queries and add some indexes. And, since the Filtered Views enforce the security model at the database level, reading from them for any reporting or custom integrations might help.

It sounds like you have a long road ahead of you to get to a supported state.