Script for Delete Stranded User Activity in Microsoft SQL Server

In this article, we will going to use a script to delete the activity table records for a specific stranded user in Dynamics GP. This will enable us not to have all the users log out of GP in order to execute these scripts. This is typically handy when we have many users signed into the system from various regions and its a tough job to have all the users sign off the system for this maintenance to be done. Please make sure that you must take a back up of DYNAMICS database before you execute this script.

DECLARE @USERID CHAR(15)

DECLARE @CMPNYNAM CHAR(30)

SET @USERID = 'testuser'

SET @CMPNYNAM = 'Fabrikam, Inc.'

/*Deleting the user record from the DEX_LOCK table*/

DELETE FROM tempdb.dbo.DEX_LOCK

WHERE session_id = ( SELECT SQLSESID

FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the DEX_SESSION table*/

DELETE FROM tempdb.dbo.DEX_SESSION

WHERE session_id = ( SELECT SQLSESID

FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the SY00801 table*/

DELETE FROM DYNAMICS.dbo.SY00801

WHERE USERID = @USERID

AND CMPANYID = ( SELECT CMPANYID

FROM DYNAMICS.dbo.SY01500

WHERE CMPNYNAM = RTRIM(@CMPNYNAM)

)

/*Deleting the user record from the SY00800 table*/

DELETE FROM DYNAMICS.dbo.SY00800

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)

/*Deleting the user record from the ACTIVITY table*/

DELETE FROM DYNAMICS.dbo.ACTIVITY

WHERE USERID = @USERID

AND CMPNYNAM = RTRIM(@CMPNYNAM)