List all emails in a particular folder in a user archive (SQL Query Help)

[MKimler]

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.

[ChrisLangevin]

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