I am implementing a Replication monitoring system

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

I am implementing a Replication monitoring system

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-18 : 08:21:16

Russell writes "I am using stored procedures to check my distribution servers for replication errors. I have the code shown below(code listing one) in a stored procedure on each of the distribution servers. The results of the stored procedure are stored in a table on the main distribution server.

I then have a separate stored procedure that just does a select all from the populated table.

Select * from ReplicationReport
Order by Time desc

The structure of which is shown below. This works as it is, however replication errors remain in the resulting table until all the entries that you see by looking at task history have status of successful. What I would like to do is to only pull out the entries for the last 10 mins, replication on the network runs every 10 mins.

COLUMN_NAME	   TYPE_NAME	LENGTH
RunDate	           varchar	10
Time	           varchar	12
ServerName	   varchar	15
Subsystem	   varchar	15
DistributionServer varchar	15
ErrorDescription   varchar	50


Code Listing 1
-----------------
Truncate table replication report

select distinct RunDate =  
+ ' '  
+ substring(convert(varchar(10), h.rundate), 7,8) 
+ '/' 
+ substring(convert(varchar(10), h.rundate), 5,2) 
+ '/' 
+ substring(convert(varchar(10), h.rundate), 3,2), 

Time = case when runtime < 100000 then  
+ '0'  
+ substring(convert(varchar(10), h.runtime), 1,1) 
+ ':' 
+ substring(convert(varchar(10), h.runtime), 2,2) 
+ ':' 
+ substring(convert(varchar(10), h.runtime), 4,2)

else  
+ ' ' 
+ substring(convert(varchar(15), h.runtime), 1,2) 
+ ':' 
+ substring(convert(varchar(15), h.runtime), 3,2) 
+ ':' 
+ substring(convert(varchar(15), h.runtime), 5,2)

End,

"Server Name" = substring(t.name, charindex('_g',t.name)+1, 10),'Subsystem'=rtrim(t.subsystem),
'Error Description'=rtrim(h.comments)
from syshistory h, systasks t
where h.taskid = t.id and h.runstatus != 1 and
convert(varchar,h.rundate) = convert(varchar,getdate(),112)
and t.subsystem='Distribution'
order by Time desc"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources