I'm attempting to get all of the SaveSetID or TransactionID (I'll take whatever at this stage) that exist in a particular folder that I specify in a WHERE clause. I'm using this to identify all of the DVS files for all of the messages in one folder of the user's archive. There are hundreds so I need to do this via SQL.
Anyone have any idea on how to accomplish this?
Thanks for your help.
The query below should do the trick. Substitute your own ArchiveID and FolderName on the first two lines. Run against the VSDB. If the EnterpriseVaultDirectory DB is not on the same SQL Server, then you will need to fully-qualify some of the joins.
I hope this is not too late to be of some help.
--Chris
DECLARE @ArchiveID varchar(112) = '14A4CB8A35DA49742BC5A98C0049CB7DE1110000evserver1.EV.LOCAL'
DECLARE @FolderName nvarchar(256) = 'Inbox'
SELECT
a.ArchiveName
,af.FolderName
,IdTransaction
,dbo.CombineSavesetId(IdChecksumHigh,IdChecksumLow,IdDateTime,IdUniqueNo,IdTransaction) AS SavesetID
FROM Saveset AS s
INNER JOIN Vault AS v
ON v.VaultIdentity = s.VaultIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root AS rv
ON rv.VaultEntryId = v.VaultID
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder AS af
ON rv.RootIdentity = af.RootIdentity
INNER JOIN ArchivePoint AS ap
ON ap.ArchivePointIdentity = s.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root AS ra
ON ra.VaultEntryId = ap.ArchivePointId
INNER JOIN EnterpriseVaultDirectory.dbo.Archive AS a
ON a.RootIdentity = ra.RootIdentity
WHERE
ra.VaultEntryId = @ArchiveId
AND af.FolderName = @FolderName
