cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Old EV6 for decommissioning.

jpergola329
Level 5

hello,

back in may i posted a question which resolved my issue by a user(Tony Sterling) posted script, pls see below.

this worked in ev8 no problem but i need it to work in ev6, i get the following error when running the script in sql

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'MAX'.
 
thanks
 

What version are you?  If running EV 10 sp2 there is a nice archiving report created after the schedule completes.

As for Retention categories, you could use this sql, it will run against the Vault Store database and you need to place the name of the archive where it says Your ArchiveName.

If you comment that line out it will run against all achives but may take some time.

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName

 

1 ACCEPTED SOLUTION

Accepted Solutions

Tremaine
Level 6
Employee Certified

Not entirely sure why you need the above SQL query, however the one below should achieve what you have above as well:

select afv.ArchiveName,emaf.FolderName,emaf.FolderPath,rc.RetentionCategoryName,ssrv.RCCount as ArcItemCount from Vault v
inner join
(select VaultIdentity,retentioncategoryidentity,count(*) as RCCount from saveset ss1
group by ss1.VaultIdentity,ss1.RetentionCategoryIdentity) ssrv
on v.VaultIdentity = ssrv.VaultIdentity
inner join EnterpriseVaultDirectory..ExchangeMbxArchiveFolderview emaf on emaf.VaultEntryId = v.VaultID
inner join EnterpriseVaultDirectory..ArchiveView afv on afv.VaultEntryId = emaf.ArchiveVEID
inner join EnterpriseVaultDirectory..RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = ssrv.RetentionCategoryIdentity
order by afv.ArchiveName

View solution in original post

9 REPLIES 9

JesusWept3
Level 6
Partner Accredited Certified
Try just doing af.folderpath instead of using a cast
https://www.linkedin.com/in/alex-allen-turl-07370146

jpergola329
Level 5

tried it and no good.

what would that look like?

thanks

 

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

It would look like this:

SELECT A.ArchiveName
       ,AF.FolderPath
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, AF.FolderPath, AF.FolderName, rc.RetentionCategoryName

jpergola329
Level 5
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

JesusWept3
Level 6
Partner Accredited Certified
What version of SQL is this? 2000?
https://www.linkedin.com/in/alex-allen-turl-07370146

jpergola329
Level 5

sql server 8.0.2039

i know its old!!

we just need to get the data off.

thanks

 

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

for clarification, do you need to get the data out of EV and put it somewhere else (PST, Exchange, another archive system) or just stats about the data for reporting purposes?

Tremaine
Level 6
Employee Certified

Not entirely sure why you need the above SQL query, however the one below should achieve what you have above as well:

select afv.ArchiveName,emaf.FolderName,emaf.FolderPath,rc.RetentionCategoryName,ssrv.RCCount as ArcItemCount from Vault v
inner join
(select VaultIdentity,retentioncategoryidentity,count(*) as RCCount from saveset ss1
group by ss1.VaultIdentity,ss1.RetentionCategoryIdentity) ssrv
on v.VaultIdentity = ssrv.VaultIdentity
inner join EnterpriseVaultDirectory..ExchangeMbxArchiveFolderview emaf on emaf.VaultEntryId = v.VaultID
inner join EnterpriseVaultDirectory..ArchiveView afv on afv.VaultEntryId = emaf.ArchiveVEID
inner join EnterpriseVaultDirectory..RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = ssrv.RetentionCategoryIdentity
order by afv.ArchiveName

jpergola329
Level 5

andrewb: both but right now need to see the what in there as it pertains to folders and retention.

ghost: the script above gave me the user name, and all there folders, with the retention of each folder

thanks