I created this blog post to discuss the issue with certain Wrk Tables not always being cleared and how to Truncate Wrk Tables in Sage 500 ERP. Before I go any further this code is provided as is. There is no warranty or guarantee provided with any of this code. This code should be tested thoroughly in a test environment.
So to provide a little background into the scenario let's take a look at the following results. This shows some SQL tables used in Sage 500 ERP to store certain information while it is being processed. Then this information should be cleaned up within the application logic. There are times, for various reasons which I will not go into here, that these tables do not get properly maintained. Sage had a SQL Script for this occasion. I believe it came out of the Sage Technical Support Group and was called 'TruncateWrkTables'. This continued to have the same name but was slightly altered after the Sage 500 ERP v7.0 release to handle some minor changes to the data structure.
You can use the following syntax to look at your Wrk tables in your Sage 500 ERP database.
-- Select the Sage 500 ERP Database
USE [sage500_app];
GO-- Look at RowCount of just WRK tables with RowCount greater than 0, also exclude 3 tables that will have some master records in them
SELECT OBJECT_NAME(object_id) AS [ObjectName],
SUM(Rows) AS [RowCount]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 -- Index_id: 0 = HEAP, 1 = Custered Index, ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
AND OBJECT_NAME(object_id) LIKE 't%Wrk'
AND OBJECT_NAME(object_id) NOT LIKE 'tapStubOverflowWrk' -- This table contains a row for AP Check Stub Overflow layout
AND OBJECT_NAME(object_id) NOT LIKE 'tapPrintChecksWrk' -- This table contains a row for AP Check layout
AND OBJECT_NAME(object_id) NOT LIKE 'tapPrintStubWrk' -- This table contains a row for AP Check Stub layout
GROUP BY [object_id]
HAVING SUM(Rows) > 0 -- if you comment this line out it will show you all of the 'Wrk' tables in the database
ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);
GO
Starting with the release of Sage 500 ERP v7.30, the application no longer contains the table upon which the 'TruncateWrkTables' process was based, making this utility no longer useful. The table of most relevance was the tsmUserSession table. This was removed when Sage started using the native SQL Server User Session tracking. You can now expose this information with the SQL Server DMV sys.dm_exec_sessions.
SELECT * FROM
sys.dm_exec_sessions
Then you can select the tables that contain a column called SessionID as well as 'Wrk' to read through and see if there are any orphaned records in the Wrk tables. These are records containing a SessionID that is no longer in use on the current SQL Server. This syntax will show you the objects (user tables) with 'Wrk' in the name and the column name of SessionID. We will link the rows from these work tables into sys.dm_exec_sessions based on SessionID.
SELECT DISTINCT obj.name
FROM sys.sysobjects obj WITH (NOLOCK)
JOIN sys.syscolumns col WITH (NOLOCK)
ON obj.id = col.id
WHERE obj.name LIKE 't%Wrk'
AND obj.type = 'U'
AND obj.name NOT LIKE 'tapStubOverflowWrk'
AND obj.name NOT LIKE 'tapPrintChecksWrk'
AND obj.name NOT LIKE 'tapPrintStubWrk'
ORDER BY obj.name
You will need to create this Stored Procedure first as this is called from the actual SQL Script that will perform the Truncate of the Wrk tables.
/**********************************************************************************
* Name: spAppExclusion.sql
* Author: Steve Conte
* Creation Date: 06/13/2000
* Copyright: "COPYRIGHT" (C) 2000 by Best Software, Inc., Confidential
* Description: This procedure excludes logged changes made to data for the
* execution duration of the stored procedure that calls it. It
* also logs results in tciDBActivityLog.
*
* Parameters: @iAppExists (smallint) - indicates if executing app already
* exists in exclusion table
* @iOperation (smallint) - 1 inserts app for exclusion,
* 2 deletes app exclusion
* @iStartTime (datetime) - beginning datetime of changes
* @iEndTime (datetime) - ending datetime of changes
* @iProcedureName (char(30)) - calling stored procedure name
* @iGeneralError (smallint) - 0 no error, 1 error
* @iGeneralErrorText (varchar(255)) - error text from stored procedure
*
* Returns: @oAppExists (smallint) - output indicates if app exclusion exists
*
*
*************************************************************************************/
SET NOCOUNT ONIF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[spAppExclusion]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spAppExclusion]
GOSET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
SET ROWCOUNT 0
GOCREATE PROCEDURE dbo.spAppExclusion
(@iAppExists smallint,
@iOperation smallint,
@iStartTime datetime,
@iEndTime datetime,
@iProcedureName char(30),
@iGeneralError smallint,
@iGeneralErrorText varchar(255),
@oAppExists smallint OUTPUT)
ASBEGIN
DECLARE @SystemDatabase char(30),
@LoginName char(30)IF @iOperation = 1
IF NOT EXISTS (SELECT ProgramName
FROM tciDBActProgName
WHERE ProgramName = APP_NAME())
BEGIN
INSERT tciDBActProgName VALUES(APP_NAME())
SELECT @oAppExists = 0
END
ELSE
SELECT @oAppExists = 1IF @iOperation = 2
BEGIN
EXEC spGetLoginName @LoginName OUTPUT
INSERT tciDBActivityLog VALUES(@iStartTime,2,@LoginName,APP_NAME(),'Best CSS - Data Modification Start.'
,NULL,@iProcedureName)
INSERT tciDBActivityLog VALUES(@iEndTime,2,@LoginName,APP_NAME(),'Best CSS - Data Modification End. General Error '+
CAST(@iGeneralError AS char(5)),@iGeneralErrorText,@iProcedureName)
IF @iAppExists <> 1
DELETE tciDBActProgName WHERE ProgramName = APP_NAME()
END
END
GO-- ALL is deprecated in SQL Server 2012 but will still work based on current support
GRANT ALL ON dbo.spAppExclusion TO public
GO
The remaining SQL syntax is the actual process of clearing the orphaned records from the Wrk tables.
/**********************************************************************************
*
* Run this script against the application db.
*
* 1. Open the script in Query Analyzer
* 2. Execute the query against the application database.
*
* This will clear orphaned rows from the Wrk tables. It can be run while
* users are logged on, but will fully function when users are not processing data.
*
*************************************************************************************/SET NOCOUNT ON
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[spAppExclusion]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spAppExclusion]
GOSET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
SET ROWCOUNT 0
GO/**********************************************************************************
* Name: spAppExclusion.sql
* Author: Steve Conte
* Creation Date: 06/13/2000
* Copyright: "COPYRIGHT" (C) 2000 by Best Software, Inc., Confidential
* Description: This procedure excludes logged changes made to data for the
* execution duration of the stored procedure that calls it. It
* also logs results in tciDBActivityLog.
*
* Parameters: @iAppExists (smallint) - indicates if executing app already
* exists in exclusion table
* @iOperation (smallint) - 1 inserts app for exclusion,
* 2 deletes app exclusion
* @iStartTime (datetime) - beginning datetime of changes
* @iEndTime (datetime) - ending datetime of changes
* @iProcedureName (char(30)) - calling stored procedure name
* @iGeneralError (smallint) - 0 no error, 1 error
* @iGeneralErrorText (varchar(255)) - error text from stored procedure
*
* Returns: @oAppExists (smallint) - output indicates if app exclusion exists
*
*
*************************************************************************************/CREATE PROCEDURE spAppExclusion
(@iAppExists smallint,
@iOperation smallint,
@iStartTime datetime,
@iEndTime datetime,
@iProcedureName char(30),
@iGeneralError smallint,
@iGeneralErrorText varchar(255),
@oAppExists smallint OUTPUT)
ASBEGIN
DECLARE @SystemDatabase char(30),
@LoginName char(30)IF @iOperation = 1
IF NOT EXISTS (SELECT ProgramName
FROM tciDBActProgName
WHERE ProgramName = APP_NAME())
BEGIN
INSERT tciDBActProgName VALUES(APP_NAME())
SELECT @oAppExists = 0
END
ELSE
SELECT @oAppExists = 1IF @iOperation = 2
BEGIN
EXEC spGetLoginName @LoginName OUTPUT
INSERT tciDBActivityLog VALUES(@iStartTime,2,@LoginName,APP_NAME(),'Best CSS - Data Modification Start.'
,NULL,@iProcedureName)
INSERT tciDBActivityLog VALUES(@iEndTime,2,@LoginName,APP_NAME(),'Best CSS - Data Modification End. General Error '+
CAST(@iGeneralError AS char(5)),@iGeneralErrorText,@iProcedureName)
IF @iAppExists <> 1
DELETE tciDBActProgName WHERE ProgramName = APP_NAME()
END
END
GOGRANT ALL ON spAppExclusion TO public
GO/**********************************************************************************
* Name: TruncateWorkTables
* Author: Steve Conte
* Creation Date: 01/31/02
* Copyright: "COPYRIGHT" (C) 2002 by Best Software, Inc., Confidential
* Description: Truncation of work tables containing orphaned rows
*
* Parameters: <Any required parameters>
*
* Returns: Success or failure text
*
* Modifications: 08/09/02 SJC Modified to accommodate all Wrk tables when no open
* sessions exist
* 12/15/03 SJC Added logic to check for posting activity
*
* 07/22/04 SJC Updated code to accommodate pending Standard tiers
*
*************************************************************************************/-- Declare variables for view creation
DECLARE @sysdb varchar(30)-- Determine system database name
-- jln 02/13/2006 This is part of the same database in version 7.0
--SELECT @sysdb = DatabaseName FROM tciSystemDatabase
SELECT @sysdb = 'mas500_app'-- jln 02/13/2006 This is part of the same database in version 7.0
-- Create tsmUserSession view for easier processing
--IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tsmUserSession]'))
-- DROP VIEW tsmUserSession
--
-- EXEC ('CREATE VIEW tsmUserSession AS
-- SELECT * FROM ' +@sysdb+'..tsmUserSession (NOLOCK)')
--
-- Create tsmLockWrk view for locking exclusion
--IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tsmLockWrk]'))
-- DROP VIEW tsmLockWrk
--
-- EXEC ('CREATE VIEW tsmLockWrk AS
-- SELECT * FROM ' +@sysdb+'..tsmLockWrk (NOLOCK)')
-- jln 02/13/2006 END-- Clear orphaned user sessions
EXEC ('EXEC ' +@sysdb+ '..spClearUserSessions')GO
-- Declare variables to be used for app exclusion
DECLARE @AppExists smallint,
@StartTime datetime,
@EndTime datetime,
@ProcedureName char(30),
@GeneralError smallint,
@GeneralErrorText varchar(255)-- Set application exclusion
-- Automatically store start time to variable
SET @StartTime = getdate()-- Describe script text in < 30 characters within quotes
SET @ProcedureName = 'TruncateWrkTables'-- Error results can be tracked using this variable
-- after each query like SELECT @GeneralError = @@Error
SET @GeneralError = 0-- Execute app exclusion procedure to exclude changes made
-- in the code from being itemized in the log.
-- Populates a single row with start time, etc.
EXEC spAppExclusion @AppExists,1,@StartTime,@EndTime,@ProcedureName,@GeneralError,@GeneralErrorText,@AppExists OUTPUT-- Declare variables
DECLARE @dbobject varchar(30),
@SessionExists tinyint-- If user sessions exist, create cursor for Wrk tables that include a SessionID column
IF EXISTS (SELECT 1 FROM tsmUserSession (NOLOCK))
BEGIN
DECLARE truncate_cursor CURSOR FORSELECT DISTINCT obj.name
FROM sysobjects obj (NOLOCK)
JOIN syscolumns col (NOLOCK)
ON obj.id = col.id
WHERE obj.name LIKE 't%Wrk'
AND obj.type = 'U'
AND col.name = 'SessionID'
AND obj.name NOT LIKE 'tapStubOverflowWrk'
AND obj.name NOT LIKE 'tapPrintChecksWrk'
AND obj.name NOT LIKE 'tapPrintStubWrk'
ORDER BY obj.nameOPEN truncate_cursor
FETCH NEXT FROM truncate_cursor INTO @dbobject
SET @SessionExists = 1
END
ELSE
-- If sessions don't exist, create cursor for all Wrk tables except check tables
BEGIN
DECLARE truncate_cursor CURSOR FORSELECT DISTINCT obj.name
FROM sysobjects obj (NOLOCK)
JOIN syscolumns col (NOLOCK)
ON obj.id = col.id
WHERE obj.name LIKE 't%Wrk'
AND obj.type = 'U'
AND obj.name NOT LIKE 'tapStubOverflowWrk'
AND obj.name NOT LIKE 'tapPrintChecksWrk'
AND obj.name NOT LIKE 'tapPrintStubWrk'
ORDER BY obj.nameOPEN truncate_cursor
FETCH NEXT FROM truncate_cursor INTO @dbobject
SET @SessionExists = 0
END-- Begin while loop for truncation
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @SessionExists = 1
BEGIN
EXEC('IF NOT EXISTS (SELECT 1
FROM ' +@dbobject+ ' DBObject (TABLOCK)
JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey)
TRUNCATE TABLE ' + @dbobject+ '
ELSE
DELETE ' +@dbobject+ '
FROM ' +@dbobject+ ' DBObject (ROWLOCK)
LEFT JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey
WHERE Session.SessionKey IS NULL')
END
ELSE
BEGIN
EXEC ('TRUNCATE TABLE ' + @dbobject)
ENDFETCH NEXT FROM truncate_cursor INTO @dbobject
END
-- Delete orphaned rows from tables where alignment data exists
EXEC ('DELETE tapStubOverflowWrk
FROM tapStubOverflowWrk DBObject (TABLOCK)
LEFT JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey
WHERE Session.SessionKey IS NULL
AND DBObject.SessionID <> 0')EXEC ('DELETE tapPrintChecksWrk
FROM tapPrintChecksWrk DBObject (TABLOCK)
LEFT JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey
WHERE Session.SessionKey IS NULL
AND DBObject.SessionID <> 0')EXEC ('DELETE tapPrintStubWrk
FROM tapPrintStubWrk DBObject (TABLOCK)
LEFT JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey
WHERE Session.SessionKey IS NULL
AND DBObject.SessionID <> 0')EXEC ('DELETE tciErrorLog
FROM tciErrorLog DBObject (ROWLOCK)
LEFT JOIN tsmUserSession Session (NOLOCK)
ON DBObject.SessionID = Session.SessionKey
WHERE Session.SessionKey IS NULL
AND DBObject.SessionID <> 0')-- Close and deallocate cursor
CLOSE truncate_cursorDEALLOCATE truncate_cursor
-- Can be called from anywhere in code with GOTO
-- Handles errors by rolling back changes and exitingErrorHandler:
IF @GeneralError <> 0
BEGIN
SELECT @GeneralErrorText = @GeneralErrorText +
' Truncation of work tables containing orphaned rows in TruncateWorkTables failed. Changes rolled back.'
-- ROLLBACK TRAN
SELECT @GeneralError = 1
END
ELSE
BEGIN
SELECT @GeneralErrorText = @GeneralErrorText +
' Truncation of work tables containing orphaned rows in TruncateWorkTables successful.'
-- COMMIT TRAN
END-- Remove application exclusion
-- Store end time to variable
SELECT @EndTime = getdate()-- Execute app exclusion procedure to populate row with end time, etc.
EXEC spAppExclusion @AppExists,2,@StartTime,@EndTime,@ProcedureName,@GeneralError,@GeneralErrorText,@AppExists OUTPUT-- Output results to user
PRINT ''
PRINT 'Update Complete'
PRINT ''
SELECT @StartTime "Start Time",@EndTime "End Time",@GeneralError "Error Number",@GeneralErrorText "Error Text"
I hope you found this useful for your Sage 500 ERP environment.