Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Trouble Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 9:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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.
Post #1516845
Posted Friday, November 22, 2013 9:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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.
Post #1516856
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse