SQL Query Help

Created: 12 Oct 2012 | Updated: 12 Oct 2012 | 2 comments

EV007

This issue has been solved. See solution.

Hi,

I'm trying to run following where Directory DB in one SQL server and Vault Store DB in another SQL server, how can we run this query in this scenario?

select COUNT(*), SUM(SS.ItemSize)/1024 'size in MB' from saveset SS

inner join ArchivePoint AP on SS.ArchivePointIdentity = AP.Archivepointidentity

inner join EnterpriseVaultDirectory.dbo.ArchiveView EVDAV on AP.ArchivePointId = EVDAV.VaultEntryId

where EVDAV.ArchiveName = 'archive'

and SS.ArchivedDate > '2012-10-11'

and SS.ArchivedDate < '2012-12-08'

Getting error

Msg 208, Level 16, State 1, Line 1

Invalid object name 'EnterpriseVaultDirectory.dbo.ArchiveView'.

Many Thanks

Discussion Filed Under:

Archiving and eDiscovery, Enterprise Vault, Tip/How to

Comments RSS Feed

Comments 2 Comments • Jump to latest comment

GertjanA Partner Accredited Certified

SQL Query Help - Comment:12 Oct 2012 : Link

Hi,

You need to use 'linked server' (I am not an DBA, but a DBA should know)

The query I use frequently looks like this:

SELECT A.ArchiveName, COUNT(S.IdTransaction) AS ArchivedItemCount, SUM(S.ItemSize/1024) AS ArchivedItemSize
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
EV02_on_server02.EVVSMailEVxxx_11.dbo.ArchivePoint AP,
EV02_on_server02.EVVSMailEVxxx_11.dbo.Saveset S

WHERE
(A.ArchiveName like '%username%')
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND S.ArchivedDate > dateAdd("m",-1,getDate())
AND S.ArchivedDate < getDate()
GROUP BY A.ArchiveName

Directorydb is on EV01 instance on server01
(where EV02_on_server02) is the instance on the 2nd SQL server
Where EVVSMailEVxxx_11 is the vaultstoredatabase

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

SOLUTION

EV007

SQL Query Help - Comment:12 Oct 2012 : Link

Hmmm Thanks,

I have done the following, worked fine for me

sp_addlinkedserver @Server=EVSQL1

Then