Need interface for displaying condition of subscriber DB to users

Does anyone know how to create something like this ...
I have transactional replication set up to maintain copies of several
production databases (located in regional offices) in our corporate office.
The users in the corporate office use the copies for reporting.
Transactional replication is set to push every 30 mins, but with WAN
connections sometimes being down, that might not always be the case. I want
to provide the corporate office users with a simple display of how current
the copies are. For example:
"The Sydney reporting database was last synchronized with the live database
22 mins ago.
The Melbourne reporting database was last synchronized with the live
database 1 day 2 hours 15 mins ago."
etc
Ideally this would take the form of a web page. Any SQL scripts out there
that can give me this data?
on your publisher issue the following in your master database
sp_serveroption 'SubscriberServerName','data access','true'
and then in your distribution database run the following
select time, entry_time from
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
msrepl_transactions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> Does anyone know how to create something like this ...
> I have transactional replication set up to maintain copies of several
> production databases (located in regional offices) in our corporate
office.
> The users in the corporate office use the copies for reporting.
> Transactional replication is set to push every 30 mins, but with WAN
> connections sometimes being down, that might not always be the case. I
want
> to provide the corporate office users with a simple display of how current
> the copies are. For example:
> "The Sydney reporting database was last synchronized with the live
database
> 22 mins ago.
> The Melbourne reporting database was last synchronized with the live
> database 1 day 2 hours 15 mins ago."
> etc
> Ideally this would take the form of a web page. Any SQL scripts out there
> that can give me this data?
>
|||Thanks, that works.
Is there any way to get this information from the subscriber? Our
distribution databases are on the same server as the publishers, and
therefore subject to the same problem that this solution is alerting people
to - that the WAN link is sometimes down.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
> on your publisher issue the following in your master database
> sp_serveroption 'SubscriberServerName','data access','true'
> and then in your distribution database run the following
> select time, entry_time from
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> msrepl_transactions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> office.
> want
> database
>
|||This proc is run on your publisher and queries your subscriber. You could
also run it on your subscriber and query the publisher like this
On your subscriber run this:
sp_serveroption 'PublisherServerName','data access','true'
select time, entry_time from
MSreplication_subscriptions,
PublisherServerName.distribution.dbo.msrepl_transa ctions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Thanks, that works.
> Is there any way to get this information from the subscriber? Our
> distribution databases are on the same server as the publishers, and
> therefore subject to the same problem that this solution is alerting
people[vbcol=seagreen]
> to - that the WAN link is sometimes down.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,[vbcol=seagreen]
there
>
|||OK thats good, I was just wondering if there was a way of getting the
information from the subcriber only - in the event that the distributor or
publisher were unreachable.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulXfReouEHA.444@.TK2MSFTNGP10.phx.gbl...
> This proc is run on your publisher and queries your subscriber. You could
> also run it on your subscriber and query the publisher like this
> On your subscriber run this:
> sp_serveroption 'PublisherServerName','data access','true'
> select time, entry_time from
> MSreplication_subscriptions,
> PublisherServerName.distribution.dbo.msrepl_transa ctions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> people
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> there
>