共用方式為

Releasing Stuck Batches and Transactions without exiting all Users

  • 發行項

David MeegoFrom 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.

A batch is held in the Posting, Receiving, Busy, Marked, Locked, or Edited status in Microsoft Dynamics GP (KB 850289) Secure Link

How to remove all the inactive sessions from the DEX_LOCK table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864411) Secure Link

How to remove all the inactive sessions from the DEX_SESSION table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864413) Secure Link

Batch status codes (BCHSTTUS) in the SY00500 Posting Definitions master table (KB 852420) Secure Link

 

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.

You might also want to look at the Automated Solutions, the links are at the bottom of the General Articles & Links page.

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.

SQL Cleanup Activity.zip

Comments

  • Anonymous
    December 01, 2008
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    December 03, 2008
    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.

  • Anonymous
    December 10, 2008
    Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft Dynamics

  • Anonymous
    December 10, 2008
    Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft

  • Anonymous
    February 22, 2009
    Posting on Rose Business Solutions Blog http://rbsgp.blogspot.com/2009/02/dynamics-gp-top-technical-support.html

  • Anonymous
    June 20, 2010
    Posting from Ron Wilson, the Real Life Dynamics User rldu.wordpress.com/.../sql-scripts-clear-users-and-activity-locks

  • Anonymous
    June 28, 2011
    Posting from Rubal at Dynamics GP Help dynamicsgphelp.com/.../microsoft-dynamics-gp-error-batch-is-marked-for-posting-by-another-user

  • Anonymous
    February 05, 2014
    I'm using this SQL Script since almost two years until now, it's a very good work. So much helpful. Thank you David.

  • Anonymous
    July 06, 2016
    The comment has been removed

    • Anonymous
      July 06, 2016
      Hi ManuelIs 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

  • Anonymous
    July 08, 2016
    Thank you!

  • Anonymous
    May 08, 2017
    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.

    • Anonymous
      May 08, 2017
      Hi GuyGlad that this article and script helped you.David