This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
From the Useful SQL Scripts Series.
When a system has a batch that is stuck as marked to post or a transaction that cannot be accessed as it says it is already being edited, the usual instructions are to exit all users from the system and then delete the contents of the activity tracking, and Dexterity session and lock tables.
These are the steps listed in Knowledge Base (KB) article 850289 (see link below) and while they work fine, they require an interruption to work for all users in all companies. On a small system in single location that can usually be organised. However, on a large system or worse a multi-national 24/7 system that can be extremely difficult to organise.
Wouldn’t it be nicer if we could just remove the activity records and reset batches only for the users currently not in the system while the rest of the users can continue their work. Well now you can.
My friend, Robert Cavill pointed out KB articles 864411 & 864413 (see links below) which can cleanup the DEX_SESSION and DEX_LOCK tables for users no longer in the system based on the ACTIVITY table while the system is still in use.
Note: The scripts suggested in KB articles 864411 & 864413, will need to be changed to use tempdb and session_id to work on a case sensitive (binary) system.
Taking this concept and bringing it to the next level, I have created a script that will remove records from the following system tables if it cannot find the user in the DYNAMICS..ACTIVITY table.
-
DEX_SESSION
-
DEX_LOCK
-
SY00800 (SY_Batch_Activity_MSTR)
-
SY00801 (SY_ResourceActivity)
For batches which have records in the SY00800 (SY_Batch_Activity_MSTR) which will be removed and the Batch Status is between 1 and 6, the script will reset the Batch Status, Marked to Post and User ID fields in the company SY00500 (Batch_Headers) table across all companies.
KB Article 852420 (see link below) provides more information on Batch Status values as well as Victoria Yudin’s post on Company/System Tables.
The script below can be used while users are logged into the application and will clean up activity records for the system and all company tables in one pass. It leverages the sp_MSforeachdb stored procedure mentioned in the Running SQL commands against all GP Company Databases post.
If you need a particular user/company to be cleaned up, just make sure there is no record for that User ID and Company ID in the DYNAMICS..ACTIVITY table before running the script.
SQL Script to remove all Activity Records for users currently not logged in
-- Dexterity Sessions Table delete S -- select * from tempdb..DEX_SESSION S where not exists ( select * from DYNAMICS..ACTIVITY A where S.session_id = A.SQLSESID) -- Dexterity Locks Table delete L -- select * from tempdb..DEX_LOCK L where not exists ( select * from DYNAMICS..ACTIVITY A where L.session_id = A.SQLSESID) -- Batch_Headers table in each company exec sp_MSforeachdb ' use [?] if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' ) begin update S set BCHSTTUS = 0, MKDTOPST = 0, USERID = '''' -- select * from SY00500 S where BCHSTTUS in (1,2,3,4,5,6) and not exists ( select * from DYNAMICS..ACTIVITY A JOIN DYNAMICS..SY01500 C ON C.CMPNYNAM = A.CMPNYNAM where S.USERID = A.USERID and C.INTERID = db_name()) and exists ( select * from DYNAMICS..SY00800 B where not exists ( select * from DYNAMICS..ACTIVITY A where B.USERID = A.USERID and B.CMPNYNAM = A.CMPNYNAM) and S.BCHSOURC = B.BCHSOURC and S.BACHNUMB = B.BACHNUMB) print '''' print ''('' + ltrim(str(@@ROWCOUNT)) + '' row(s) affected) - Database '' + db_name() end ' -- SY_Batch_Activity_MSTR table delete B -- select * from DYNAMICS..SY00800 B where not exists ( select * from DYNAMICS..ACTIVITY A where B.USERID = A.USERID and B.CMPNYNAM = A.CMPNYNAM) -- SY_ResourceActivity table delete R -- select * from DYNAMICS..SY00801 R where not exists ( select * from DYNAMICS..ACTIVITY A JOIN DYNAMICS..SY01500 C ON C.CMPNYNAM = A.CMPNYNAM where R.USERID = A.USERID and R.CMPANYID = C.CMPANYID)
If you want to see the records that will be deleted or changed, you can use — to comment out the delete and update lines and remove the — from the select * lines.
For more information please see the KB articles referenced in this post.
Batch status codes (BCHSTTUS) in the SY00500 Posting Definitions master table (KB 852420)
The script is also available as an attachment at the bottom of this post.
While this is not strictly development related, this is something that should make the job of a system administrator much easier. Once you are comfortable with this script, you could schedule it to occur on a regular basis using a SQL Agent job.
Let me know if you find this useful.
David
04-Dec-2008: Updated script to have [?] on the use statement as suggested by Andrew Cooper’s comment.
17-Mar-2022: Updated Script to use colour coding and fix quotes that have been changed to extended characters.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
Posting from the Dynamics GP Blogster
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
LikeLike
This is an extremely useful script.
I would propose one amendment however – that the "?" in "use ?" is enclosed in square-braces, as per "use [?]".
This ensures that databases with hyphens in the name are handled correctly.
Other than that, another high-quality and well written post!
LikeLike
This is a great article and a very useful script for all the technical consultants who directly support their customers.
We come across these type of issues very often with our customers.
Thanks to David.
LikeLike
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft Dynamics
LikeLike
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft
LikeLike
Posting on Rose Business Solutions Blog
http://rbsgp.blogspot.com/2009/02/dynamics-gp-top-technical-support.html
LikeLike
Posting from Ron Wilson, the Real Life Dynamics User
rldu.wordpress.com/…/sql-scripts-clear-users-and-activity-locks
LikeLike
Posting from Rubal at Dynamics GP Help
dynamicsgphelp.com/…/microsoft-dynamics-gp-error-batch-is-marked-for-posting-by-another-user
LikeLike
I'm using this SQL Script since almost two years until now, it's a very good work. So much helpful.
Thank you David.
LikeLike
Has something changed with the script recently. It used to work perfectly. Now I get incorrect syntax errors (Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘—’.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘—’.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ‘—’.
Msg 911, Level 16, State 1, Line 23
Database ‘?’ does not exist. Make sure that the name is entered correctly.)
LikeLike
Hi Manuel
Is your System Database still called DYNAMICS? this script uses DYNAMICS, if you have changed your system database name (as is allowed in the newer versions), you will need to edit the script accordingly.
David
LikeLike
Thank you!
LikeLike
Just thought I’d say thanks. Have an older Dynamics system still running and experienced this issue. The script worked like a charm. Ran it in select mode, and sure enough there was just the one record, and it was related to the one that was stuck. Ran it in update & delete mode and it cleared the issue. Nice to not have to force everybody out of the system to use the MS recommended approach.
LikeLike
Hi Guy
Glad that this article and script helped you.
David
LikeLike