Cleanup Old DSM Reporter's Report Results from the Database.

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

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