Forum Discussion

ZS1's avatar
ZS1
Level 3
4 years ago

Delete Archives with zero items

I have EV 11.0.1 running on SQL Server 2014 and am looking for a sql query to delete archives with zero items. We wont be upgrading to EV 12.

I can list those with the following query:

SELECT A.ArchiveName "Archive Name",
R.VaultEntryID "Archive ID",
AP.ModifiedDate "Last Modified",
AP.HighestIndexSeqNo "Highest Index Num",
AP.ArchivedItems "Archived Items"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
EVVSEVMBAVS01_1.dbo.ArchivePoint AP
WHERE AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND AP.ArchivedItems = 0

I need to add the part where the ArchiveStatus will get changed from 1 to 4 (mark for deletion).

 

 

 

  • ZS1's avatar
    ZS1
    4 years ago

    This worked.

     

    UPDATE EnterpriseVaultDirectory.dbo.ArchiveView
    SET ArchiveStatus = 4
    FROM XXXXXXXX.dbo.ArchivePoint AS ap
    JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
    ON av.VaultEntryId = ap.ArchivePointId
    WHERE ap.ArchivedItems = 0

    • ZS1's avatar
      ZS1
      Level 3
      4 years ago

      6000 plus

      I think I have it

      USE EnterpriseVaultDirectory
      UPDATE Archive
      SET ArchiveStatus = 4
      WHERE RootIdentity = (
      SELECT AP.ArchivedItems "Archived Items"
      FROM EnterpriseVaultDirectory.dbo.Archive A,
      EnterpriseVaultDirectory.dbo.Root R,
      XXXXXXXX.dbo.ArchivePoint AP
      WHERE AP.ArchivedItems = 0)

      I will have a sql dba confirm it before running it.

       

      • ZS1's avatar
        ZS1
        Level 3
        4 years ago

        That did not work either.

        Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
        The statement has been terminated.