SQL Query Help

[ia01]

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

[GertjanA · Accepted solution]

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

[ia01]

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'