Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

SQL Query Help

Created: 12 Oct 2012 • Updated: 12 Oct 2012 | 2 comments
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
 

Comments 2 CommentsJump to latest comment

GertjanA's picture

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
ia01's picture

Hmmm Thanks,

I have done the following, worked fine for me

 

 

sp_addlinkedserver @Server=EVSQL1

Then

 

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

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

inner join EVSQL1.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'