WorldServer - stored procedure for SQL and Oracle Databases to be able to send long-running, executing jobs in the workflow Engine into Autoerror

Sometimes we see jobs in the Workflow Engine Queue executing for several hours and not completing. What can we do to unblock these jobs and make sure the execution finishes?

 

Important: this action should be done by an experienced Database Administrator and at your own risk.

Below are the stored procedures for, respectively, SQL and Oracle Databases.

Once loaded in the Worldserver Database, you will be able to apply the workaround described in Step #3 of this article:

WorldServer - how to send a Task hanging in the Workflow Engine Queue into Autoerror

This is the stored procedure for the SQL Database (note: adapt the DB name to the relevant one, the example below is for SNJWS037_WS):
 

USE [SNJWS037_WS]
GO
/****** Object:  StoredProcedure [dbo].[spMoveProjecttasktoAutoError]    Script Date: 06/08/2019 09:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[spMoveProjecttasktoAutoError] (
    @vInProjectId INT, 
    @vInTaskNuminProject INT) 
AS
    BEGIN
    DECLARE @err integer

--       -- do the update for the redo steps
    --vStmtDesc := 'copying active values of taskstep and workflowstep to auto error redo values';
    update tasks
    set failedtaskautostepid = (select currenttaskstepid
    from tasks
    where tasknuminproject = @vInTaskNuminProject
    and tasks.projectid = @vInProjectId), 
    failedworkflowautostepid = (select currentworkflowstepid
    from tasks
    where tasknuminproject = @vInTaskNuminProject
    and tasks.projectid = @vInProjectId)
    where taskid = (select tasks.taskid
    from tasks
    where tasknuminproject = @vInTaskNuminProject
    and tasks.projectid = @vInProjectId);
    select @err=@@error
    IF (@err!=0)
    BEGIN
        RAISERROR ('spMoveProjecttoAutoError: Failure (Error=%d)',16,-1, @err)
        ROLLBACK
        return
    END  

    -- do the update for the redo steps
    --vStmtDesc := 'placing auto error values in actve taskstep and active workflowstep';
    update tasks
    set currenttaskstepid = (select taskstepid from tasksteps where taskid = (select  taskid from tasks
    where tasks.projectid = @vInProjectId and tasknuminproject = @vInTaskNuminProject)
    and workflowstepid = (select TOP 1 workflowstepid from workflowsteps,workflowsteptypes
    where workflowid = (select workflowid from tasks where tasknuminproject = @vInTaskNuminProject
    and tasks.projectid = @vInProjectId)
    and  workflowsteps.workflowsteptypeid = workflowsteptypes.workflowsteptypeid
    and name = 'Automatic Error')
    ),
    currentworkflowstepid = (select workflowstepid from workflowsteps,workflowsteptypes
    where workflowid = (select workflowid from tasks where tasknuminproject = @vInTaskNuminProject
    and tasks.projectid = @vInProjectId)
    and  workflowsteps.workflowsteptypeid = workflowsteptypes.workflowsteptypeid
    and name = 'Automatic Error'),
    taskautostepstate = 0
    where taskid = (select tasks.taskid
    from tasks
    where tasknuminproject = @vInTaskNuminProject and tasks.projectid = @vInProjectId);

    -- set the failed step to started
    update tasksteps
    set state = 2
    where taskstepid =  (select currentTaskStepId
    from tasks
    where tasknuminproject = @vInTaskNuminProject and tasks.projectid = @vInProjectId);

    IF (@err!=0)
    BEGIN
        RAISERROR ('spMoveProjecttoAutoError: Failure (Error=%d)',16,-1, @err)
        ROLLBACK
        return
    END  

    END

This is the stored procedure for the Oracle Database:
 

CREATE OR REPLACE PROCEDURE spMoveProjecttasktoAutoError (vInProjectId IN number, vInTaskNuminProject IN number) IS
        vStmtDesc VARCHAR2(128);

-- exec spMoveProjecttasktoAutoError (ProjectId, TaskNum)
        
BEGIN

       -- do the update for the redo steps
    vStmtDesc := 'copying active values of taskstep and workflowstep to auto error redo values';
    update tasks
    set failedtaskautostepid = (select currenttaskstepid
    from tasks
    where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId), 
    failedworkflowautostepid = (select currentworkflowstepid
    from tasks
    where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId)
    where taskid = (select tasks.taskid
    from tasks
    where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId);


    -- do the update for the redo steps
    vStmtDesc := 'placing auto error values in actve taskstep and active workflowstep';
    update tasks
    set currenttaskstepid = (select taskstepid from tasksteps where taskid = (select  taskid from tasks
    where tasks.projectid = vInProjectId and tasknuminproject = vInTaskNuminProject)
    and workflowstepid = (select workflowstepid from workflowsteps,workflowsteptypes
    where workflowid = (select workflowid from tasks where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId)
    and  workflowsteps.workflowsteptypeid = workflowsteptypes.workflowsteptypeid
    and name = 'Automatic Error')
    and rownum = 1), 
    currentworkflowstepid = (select workflowstepid from workflowsteps,workflowsteptypes
    where workflowid = (select workflowid from tasks where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId)
    and  workflowsteps.workflowsteptypeid = workflowsteptypes.workflowsteptypeid
    and name = 'Automatic Error'),
    taskautostepstate = 0
    where taskid = (select tasks.taskid
    from tasks
    where tasknuminproject = vInTaskNuminProject and tasks.projectid = vInProjectId);

    -- set the failed step to started
    update tasksteps
    set state = 2
    where taskstepid =  (select failedtaskautostepid
    from tasks
    where tasknuminproject = vInTaskNuminProject
    and tasks.projectid = vInProjectId);
    

    commit;
   

EXCEPTION
WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20099,'spMoveProjecttoAutoError: Failure '||vStmtDesc||', transaction terminated: ('||to_char(SQLCODE)||') '||SQLERRM);
 ROLLBACK;
END;
/