Releasing Stuck Batches and Transactions without exiting all Users
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.
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) 
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)
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)
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.
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.
