locked
Cancel Workflow through Database RRS feed

  • Question

  • Hi,

    I have some 5000+ workflows for the year 2014, which are in waiting state and I want cancel all those waiting workflows at one go.

    So I thought of doing it through database and the query for that is

    UPDATE AsyncOperation
    SET       StateCode=3 ,StatusCode=32
    WHERE  StateCode=1 AND StatusCode=10 AND StartedOn <='2014-12-31'

    But I am not sure what is the other impact.

    If anyone has done anything regarding this, please suggest about this approach or any other solutions is also appreciable.

    Thanks & Regards,

    Nibedita

    Thursday, March 26, 2015 9:21 AM

All replies

  • Update directly the CRM Database is unsupported.

    Probably it's better to write a console application that will cancel that workflows.


    My blog: www.crmanswers.net - Rockstar 365 Profile

    Thursday, March 26, 2015 9:25 AM
  • Hi,

    There is a KB Article for this.

    https://support.microsoft.com/en-us/kb/968520?wa=wsignin1.0

    I have taken the same and added a condition to only the records of the previous month.

    IF EXISTS (SELECT name from sys.indexes
                      WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
          DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
    GO
    CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
    ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType],[completedon])
    GO
    -- StateCode 3 = Completed, StautsCode = 30 Succeeded, 31 Failed and 32 Cancelled
    --while(1=1)
    begin
    declare @DeleteRowCount int = 2
    declare @rowsAffected int
    declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
    insert into @DeletedAsyncRowsTable(AsyncOperationId)
    Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
    where
      OperationType in (10)
      AND StateCode = 3
    /*add in StatusCode of 31 if you also want to delete failure records*/
      AND StatusCode in (30, 32)
      AND completedon < DATEADD(MONTH, -1, GETDATE())

    select @rowsAffected = @@rowcount
     
    delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
    where
      W.AsyncOperationId = d.AsyncOperationId            
     delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
    where
      B.AsyncOperationId = d.AsyncOperationId
    delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
    where
      WS.AsyncOperationId = d.AsyncOperationID
     delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
    where
      A.AsyncOperationId = d.AsyncOperationId

      --
    /*If not calling from a SQL job, use the WAITFOR DELAY*/
    if(@DeleteRowCount > @rowsAffected)
      return
    else
       WAITFOR DELAY '00:00:02.000'
    end

    Regards.

    Gopinath

    http://mscrmtechie.blogspot.in/


    Friday, March 27, 2015 5:41 AM