DSM Reporter: How to cleanup old report results from the database.

Document ID : KB000004189
Last Modified Date : 05/03/2018
Show Technical Document Details
Issue:

After a long time running reports using DSM Reporter, the number of result tables in the database may be increasing.  This document provides a T-SQL script, to automate cleaning up DSM Reporter result tables and views that may have accumulated over time.  By using the script, it avoids the user from having to manually cleanup the aged result tables using the DSM Reporter GUI, or manually cleaning up these objects from SQL.

Environment:
All versions of Client Automation using a Microsoft SQL database.
Resolution:

Run the following script in MS-SQL, against the MDB for Client Automation.  Set the variable "@numdays" to the desired age of report results to be cleaned up:

Setting a value of @numdays=30 will cleanup all report results 30 days, and older.
Setting a value of @numdays=0 will cleanup ALL report result data.

 

 

-- Declare variables
DECLARE @numdays INT
DECLARE @tplid INT
DECLARE @resid INT
DECLARE @tblname NVARCHAR(64)
DECLARE @sql VARCHAR(400)
 
-- Specify the age for result table cleanup
SET @numdays=30
 
-- Iterate each result record
DECLARE Result_Cursor CURSOR for
  SELECT tplid, resid, tblname
  FROM rpresult
  WHERE created < DATEDIFF(s, '1970-01-01', GETUTCDATE()) - (@numdays * 86400)
OPEN Result_Cursor
FETCH NEXT FROM Result_Cursor INTO @tplid, @resid, @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
 
  -- Delete result record
  DELETE FROM RPRESULT WHERE TPLID=@tplid AND RESID=@resid
 
  -- Delete filter record
  DELETE FROM RPFILTER WHERE TPLID=@tplid AND RESID=@resid
 
  -- Drop the result table
  SET @sql='DROP TABLE ca_itrm.' + @tblname
  EXEC (@sql)
  DELETE FROM RPTABLES WHERE TBLNAME=@tblname
  SET @sql='DROP TABLE ca_itrm.' + @tblname + '_M'
  EXEC (@sql)
 
  -- Drop the table record
  DELETE FROM RPTABLES WHERE TBLNAME=@tblname + '_M'
 
FETCH NEXT FROM Result_Cursor INTO @tplid, @resid, @tblname
END
CLOSE Result_Cursor
DEALLOCATE Result_Cursor
 
-- Iterate each temp view
DECLARE Temp_Cursor CURSOR FOR
SELECT u.name + '.' + o.name
FROM dbo.sysobjects o, dbo.sysusers u
WHERE o.name like 'rp_tmp_%' and crdate < getdate() - 1
AND o.uid=u.uid
AND o.type='V'
 
OPEN Temp_Cursor
FETCH NEXT FROM Temp_Cursor
INTO @tblname
WHILE @@FETCH_STATUS=0
BEGIN
 
   -- Drop temp view
   SET @sql='DROP VIEW ' + @tblname
   exec(@sql)
 
FETCH NEXT FROM Temp_Cursor
INTO @tblname
END
CLOSE Temp_Cursor
DEALLOCATE Temp_Cursor
 
-- Iterate each temp table
DECLARE Temp_Cursor CURSOR FOR
SELECT u.name + '.' + o.name
FROM dbo.sysobjects o, dbo.sysusers u
WHERE o.name like 'rp_tmp_%' and crdate < getdate() - 1
AND o.uid=u.uid
AND o.type='U'
 
OPEN Temp_Cursor
FETCH NEXT FROM Temp_Cursor
INTO @tblname
WHILE @@FETCH_STATUS=0
BEGIN
 
   -- Drop temp view
   SET @sql='DROP TABLE ' + @tblname
   exec(@sql)
 
FETCH NEXT FROM Temp_Cursor
INTO @tblname
END
CLOSE Temp_Cursor
DEALLOCATE Temp_Cursor
 
End of technical document.