Hi All,

Environmental interruptions can happen at any time and if they occur the moment that a user is submitting or approving a transaction in workflow, it can leave your workflow tables out of synch.  In this blog I wanted to provide you with some general steps that we use for any type of interrupted workflow transaction that is stuck.  

If this occurs, usually the user will get an error message similar the one below if they try to recall, approve, reject or submit the stuck transaction.  

“[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'set' [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'WorkflowInstanceID' table XXXX.dbo.WF30100; column does a”

What we need to do in this situation is clear the 4 workflow tables specific to the stuck transaction.   

Workflow Instance Master

Workflow_Instance

WFI10002

Workflow

Workflow Step Instance Table

Workflow_Step_Instance

WFI10003

Workflow

Workflow Tasks Table

Workflow_Tasks

WFI10004

Workflow

Workflow History

Workflow_History

WF30100

Workflow

How do we find the records associated with our transaction?  For all workflow types, we hold a Workflow Business Object Key (WfBusObjKey) that links the workflow tables back to the transaction.  

1. To start, use the script below to find the Workflow Instance Master (WFI10002) for your stuck transaction.  In the script you will replace XXXXXXX with the document number, batch id, or employee id that you are attempting to fix. 

  • For Batch Workflows, search on the Batch ID. 
  • For Purchasing, Payables, Sales, and RM documents search on the Document Number. 
  • For Timecards, Timesheets, Employee Expenses, and Employee ESS in Payroll, search based on the Employee ID.

NOTE: Make sure to leave the % % signs on each side as some business objects contain more than just the document number. 

SELECT Workflow_Status, WfBusObjKey, Workflow_Type_Name,
( 'select Workflow_Status, * from ' + Trim(CONVERT(VARCHAR, TBLPHYSNM, 102)) + ' '
+ Trim(Cast(Workflow_Where_Clause AS VARCHAR(250) )) ) AS 'TransactionSelect', *
FROM WFI10002
WHERE WfBusObjKey LIKE '%XXXXXXX%'

Below is an example for Payroll Timecards:  If the employee with the issue was ACKE0001, I would use the following for my search criteria. 

         WHERE WfBusObjKey LIKE '%ACKE0001%'

You will receive more than 1 result in many cases, so you will have to select the record that is having this issue.  In my case it's the employee's Timecard for Period 7 in the Pay Schedule year 2027.  In most cases with Payroll, you are trying to fix the last record on this list so if you are unsure if your pay schedule year's line up with your calendar year, but 

There are 2 important pieces of information that we need from these SQL results. 

  • The green highlighted column is our WorkflowInstanceID that is going to help us clear the workflow tables for this transaction.
  • The blue highlighted column has a select statement that you can copy and paste out of the results to execute and find your transaction header. 

BEFORE PROCEEDING TO STEP 2!!!!  ANY SQL UPDATES OR DELETES USING THE SCRIPTS BELOW SHOULD BE PROCESSED FIRST IN A TEST COMPANY WITH A COPY OF LIVE: KB - Set up a test company that has a copy of live company data for Microsoft Dynamics GP by using Microsoft SQL Server

2.  Let's take the WorkflowInstanceID from our green highlighted SQL results enter it into the script below to delete the following tables from SQL for this transaction.

delete WFI10002 where WorkflowInstanceID = 'xxxxxxxxxxxxxxxxxxxx'
delete WFI10003 where WorkflowInstanceID = 'xxxxxxxxxxxxxxxxxxxx'
delete WFI10004 where WorkflowInstanceID = 'xxxxxxxxxxxxxxxxxxxx'
delete WF30100 where WorkflowInstanceID = 'xxxxxxxxxxxxxxxxxxxx'

3.  Now we take our blue highlighted column which is a SQL select statement to find your GP transaction.  And we can use this to update our Workflow Status to be a ‘1’ which means ‘Saved’. If it is not already.   Examples below: 

select Workflow_Status, * from UPR10500 where UPR10500.EMPLOYID='ACKE0001' and UPR10500.Pay_Schedule='WEEKLY' and UPR10500.PERIODID='7' and UPR10500.YEAR1='2027'

If the Workflow_Status is not 1, then note the DEX_ROW_ID on the end of the select statement, and update the Workflow_Status = '1'

update UPR10500 set Workflow_Status = '1' where DEX_ROW_ID = 'XXXXXXX'

NOTE: My example is the UPR10500 because my example is Payroll Timecards.  This table will differ for every workflow type but the blue highlighted column in the script from step 1, identifies the table for you.  

4. Once the updated is processed, your transaction is now available to be submitted again, and it is as if the workflow never happened as the workflow history has been removed.  You can now resubmit the transaction.  

Below I have a video walking through this process if you are more of a visual learner, please check it out!  

(Video to be released on a future date on our Microsoft Dynamics GP Support YouTube Playlist)

Feel free to check out more troubleshooting videos created by our support team on the following link. 

Dynamics GP - YouTube

Thanks!

Isaac Olson
Microsoft Support