Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2022 Release Wave 2Check out the latest updates and new features of Dynamics 365 released from October 2022 through March 2023
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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 Step Instance Table
Workflow Tasks Table
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.
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 WFI10002WHERE 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.
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
Isaac OlsonMicrosoft Support
Business Applications communities