If the Batch Activity view in the Activity Locks module shows "??? (SalesPad)" as a User ID, a batch may be orphaned in SalesPad.
Batch Activity view in Modules > Activity Locks:
To remove orphaned batches, run the following SQL Query against the company database:
BEGIN TRAN
DELETE a FROM DYNAMICS..SY00800 AS a WHERE isnumeric(USERID) = 1 AND BCHSOURC =
'Sales Entry'
-- don't delete rows that have a dex_lock record (you can delete w/ activity locks)
AND NOT EXISTS (SELECT 1 FROM tempdb..dex_lock AS d WHERE d.row_id=a.USERID AND table_path_name like '%SOP10100')
COMMIT TRAN
Important Reminders and Suggestions:
- This script is considered "as is" and any changes/modifications that might be needed would require a signed quote.
- If you are required to change a Security Option or Setting, your users will need to restart SalesPad before the changes take effect.
- Please install this on a test machine and run it against a test database before using it on your live system.
- You should always make sure you have a database backup prior to installing new software.
- Database Triggers, after final delivery, are the responsibility of the dealer/customer. SalesPad will not be responsible for maintaining copies of custom database objects.
- C# scripts, after final delivery, are the responsibility of the dealer/customer.
SalesPad Support
Comments
Tim Andaya
/* You might want to evaluate this script and validating it against SP to see if it will be useful: */
--/**
--SQL server maintains a record of active user sessions in the table SYSPROCESSES from the MASTER database,
--inside sysprocesses there is a column named “login name” and we base our script on sysprocesses to
--clear the ACTIVITY table from the DYNAMICS database.
--Once ACTIVITY table has been cleaned out we are ready to clean 2 tables from the TEMP database first we
--clean DEX_SESSION and then DEX_LOCK in order to eliminate locks and processes in temp tables.
--The next step is to clean batch activity (SY00800) and resource activity (SY00801) in order to have a valid
--session clean up.
---- Reeferencces
---- A batch is held in the Posting, Receiving, Busy, Marked, Locked, or Edited status in Microsoft Dynamics GP
---- (KB 850289)
----https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;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)
----https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;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)
----https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;864413
--**/
--/** Delete orphans from Dynamics **/
delete from DYNAMICS..ACTIVITY
where USERID not in
(select loginame from master..sysprocesses)
delete from tempdb..DEX_SESSION
where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK
where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800
where USERID not in
(select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
where USERID not in
(select USERID from DYNAMICS..ACTIVITY)
/* Regards, */