Please visit support.cavallo.com

Start a conversation

Clearing Orphaned Batches Activity Locks

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.
Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. SalesPad Support

  2. Posted
  3. Updated

Comments

  1. 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, */