November 22, 2013 at 9:11 am
Hi Everyone,
I'm a JR. DBA (MSSQL)
As so I'm still getting the hang out TSQL. I am trying to write a query that will sum up the important stats of replication, so I can later put them in a report.
select s.Name as Publisher,a.Publisher_DB as Published_DB, COUNT(a.article) as Article_Count,p.Publication
from MSarticles a join MSpublications p on
a.publication_id = p.publication_id join
sys.servers s on p.publisher_id = s.server_id
group by s.name,a.publisher_db, p.publication
The query above gets the publisher, published_db,article_count, and publication name. My boss wants me to add somethings that will include the status of the agents. So I tried adding a case expression on the MSreplication_monitordata.status column. However now when I run the query I get twice as many results, repeated rows of information and I don't get why. Can someone please help? Below is the code with added case.
SELECT s.Name as Publisher,a.Publisher_DB as Published_DB, COUNT(a.article) as Article_Count,p.Publication,
case rm.status
when '1' then 'Started'
when '2' then 'Succeeded'
when '3' then 'In Progress'
when '4' then 'Idle'
when '5' then 'Retrying'
when '6' then 'Failed'
End
from MSarticles a join MSpublications p on
a.publication_id = p.publication_id join
sys.servers s on p.publisher_id = s.server_id join
MSreplication_monitordata as rm on p.publication_id = rm.publication_id
group by s.name,a.publisher_db, p.publication,rm.status
On a non related note, anyone know a good source for policy management templates and common uses? Happy Friday.
November 22, 2013 at 9:26 am
Well shoot I hate it when I post too soon! I found it was returning double because there where two agent types per publication, so a where clause on the type field fixed it.
Now, how can I delete this post.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply