I often come across this request from GP Users:
This happens when the User try to reconcile from CBM Reconcile. And once he/she selects the Checkbook which needs to be reconciled.
After some SQL Profiling & Dex Script Log, I found the following:
The table CB100006 contains the User Checkbook Activity records, as and when a User reconciles a Checkbook. This table for some reason is not cleared properly. Sometimes:
1. If you open the form, enter the Checkbook ID and just close it without any activity, this record is not cleared.
2. If you open the form, enter the Checkbook ID and just close GP directly without any activity, this record is stuck.
The above scenarios are faced by me and have not heard of this from any other consultants, so the above need not be recreated consistently.
I. Consultants must open SQL Management Studio, log on to the Data Server and connect to relevant company DB.
II. Run the following queries against DYNAMICS and the respective companies:
This below queries will delete all stranded and unwanted SQL Sessions.
DELETE TEMPDB..DEX_LOCK WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)
DELETE TEMPDB..DEX_SESSION WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)
The below queries will ensure that the respective user, against whom the error message was thrown, would be cleared from GP Application Session(s).
SELECT * FROM DYNAMICS..ACTIVITY WHERE USERID = ‘[username shown in the CBM error message]’
–Ask that user to log off, if this user has logged on for the day.
III. Run the below query to clear the CBM Checkbook Lock:
DELETE CB100006 WHERE USERID = ‘[username shown in the CBM error message]’
DELETE CBEU1020 WHERE CHEKBKID = ‘[stuck checkbook ID]’ AND USERID = ‘[username shown in the CBM error message]’
That’s it. We are good to go with our Checkbook Reconciliation in CBM.
NOTE: I will be posing another article on CBM Payments Batch Lock Error (which is identical to this error message).