Data Versioning Transactional Replication

Hi,
I've got a requirement to know when some data has been propagated to
all my (read-only) subscribers. This is because the data will be
cleaned, and the subscribers shouldn't "use" the new version for
certain queries until I know that the new data is there.
I've created a model where I have a "version" table with an identity,
that gets updated by the log reader every time it reads. I check the
version at the time the data changes are finished. Then, when i save
the version with the query that i want to run at the subscriber, and
wait until the version number is greater than or equal to my version
number.
This model seems to work, it requires me to add some steps to the
distribution job, but I'm wondering if there is a better way? Ie,
some form of replication that doesn't require me to custom code the
versioning scheme of the data.
Thx,
Doug
If the data is updated on the publisher in discrete batches you can always
wait for the distribution agent to complete. Other than that the scheme you
have implemented sounds like the best way to go. SQL 2005 will have tracer
tokens that might help with your requirement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Douglas Tarr" <douglas.tarr@.gmail.com> wrote in message
news:c33eac8b.0409070825.1654dcb3@.posting.google.c om...
> Hi,
> I've got a requirement to know when some data has been propagated to
> all my (read-only) subscribers. This is because the data will be
> cleaned, and the subscribers shouldn't "use" the new version for
> certain queries until I know that the new data is there.
> I've created a model where I have a "version" table with an identity,
> that gets updated by the log reader every time it reads. I check the
> version at the time the data changes are finished. Then, when i save
> the version with the query that i want to run at the subscriber, and
> wait until the version number is greater than or equal to my version
> number.
> This model seems to work, it requires me to add some steps to the
> distribution job, but I'm wondering if there is a better way? Ie,
> some form of replication that doesn't require me to custom code the
> versioning scheme of the data.
> Thx,
> Doug