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.
| Author |
Topic |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2005-03-09 : 14:53:46
|
| I am new to replication so please bear with me.We have a production transactional db for our online order entry system. We have recently implemented a beefy 8-way monster reporting server that will be used to A) replicate certain OLTP tables, and B) summarize for OLAP uses. What I want is to be able to run a report daily that outlines what was replicated, when, and if there are problems. Is this something that I can pull out of system tables with SQL or am I in for something more evil?Thank you for any assistance.James |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-09 : 15:39:46
|
| No you can not pull this stuff out as this is stored in the distribution database temporarily then once replicated it is gone. So you'd need to log this on your own. But you can go to the replication monitor to view any errors and check its status.Tara |
 |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2005-03-09 : 15:53:26
|
| Thanks. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2005-03-09 : 16:18:25
|
| I found what I needed in the history and other tables:select PublicationName = da.name, PublicationDatabase = a.publisher_db, a.article, a.destination_object, dh.comments, CurrentStatus = case when dh.runstatus = 1 then 'starting' when dh.runstatus = 2 then 'succeed' when dh.runstatus = 3 then 'running' when dh.runstatus = 4 then 'Idle' when dh.runstatus = 5 then 'retry' when dh.runstatus = 6 then 'failed' end, thyme.max_start_time, dh.delivered_transactions, dh.delivery_ratefrom MSdistribution_history dh inner join ( select agent_id, Max_Start_Time = max(start_time) from msdistribution_history group by agent_id ) as thyme on ( dh.agent_id = thyme.agent_id and dh.start_time = thyme.Max_Start_Time ) inner join msdistribution_agents da on ( da.id = dh.agent_id and dh.start_time = thyme.Max_Start_Time ) inner join MSarticles a on ( da.publisher_id = a.publisher_id and da.publisher_db = a.publisher_db )where dh.runstatus in (2, 3, 5, 6)order by a.article, dh.runstatus |
|
|
|
|
|
|
|
|
Report on Replication