Logic problem - Please help :-(

Hi all,
I have a situation that I need some help with as soon as possible. If
youre good at logic puzzles then please help as soon as you have a
minute :-)
As some of you know I have a situation in which I need to copy data from
a table in QuickRes, to an identical table in a new database (called
Arrivals). The databases are on physically separate machines. For
operational reasons, the query that copies the data over from QuickRes
to Arrivals will be kicked off by a Web Service, which will then return
the data as XML.
I only want to copy changed rows. Each time a row is changed, its
LastUpdated field is changed to represent the time the update took
place. The idea is simply that each time the synchronisation process
occurs, it will copy across any rows changed since the last time the
sync process ran.
I'm worried about this though because it seems to me that there might be
the possibility of missing some changes under the following
circumstances (or some permutation of them anyway):
Consider:
Time Event
10:46:10 The query to copy all changed rows begins. It starts
returning any rows that have been updated since, say, 10:31:00.
10:46:11 Under a separate thread, an update statement us executed
that changes a row in the target table. It's LastUpdated field is
changed to 10:46:11. The synchronisation query does *not* detect this
row change because it is just about to complete.
10:46:12 The synchronisation process completes at 10:46:12
The next time the sync process executes, its going to request any rows
changed since 10:46:12.
This will *not* include the row changed in step two because it's
LastUpdated value is 10:46:11
In other words, this update is lost and won't be brought over.
Questions:
1. Have I identified a real problem here, or am I worried about nothing
- perhaps because SQL Server somehow "magically" takes care of stuff
like this. It seems like a real problem to me, but I'm not very good at
logic stuff like this!
2. Assuming there is a real problem - what do I need to do to fix it?
Would putting the sync query in a transaction prevent the problem? I've
thought about that one and it seems that even in a transaction, because
the computer is so fast, the times entered into the last updated field
could still cause this problem.
I did think about using the actual Timestamp data type, but I remember
reading somewhere that the timestamp is based on the machine that it was
generated on. I was worried that it might not work because of there
being two machines involved in the process.
I know how to deal with the synchronisation process in several different
ways but for the moment were assuming that typical sync methods such as
transactional replication are off the table.
Any help or advice would be very greatly appreciated :-)
Many thanks
Kindest Regards
SimonJust thinking out loud, why don't start the sync process next time
to take records having time stamp > 10:46:10|||Simon,
why don't you use replication?|||Alexander Kuznetsov wrote:
> Simon,
> why don't you use replication?
>
Hi Alex,
I would like to use replication but my boss has a thing about web
services! He wants it done that way.
I may have to kick up a stink if there's no sure fire way to do it the
webservice way...
Thanks|||Ahhhh...
The old "newer technology is better" solution. I could go on and on about
how the database is built for replication, tried and tested, and web
services only add extra, unnecessary layers of complexity and ten times the
code (not to mention and extra server and all sorts of extra network
traffic), but I'll let other folks who are more knowledgeable about
replication do that. A separate post on the benefits of replication VS a
web service should get all sorts of expert responses that you can show to
your boss as justification for doing it the right way.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OgJ4EfbeGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Alexander Kuznetsov wrote:
>
> Hi Alex,
> I would like to use replication but my boss has a thing about web
> services! He wants it done that way.
> I may have to kick up a stink if there's no sure fire way to do it the
> webservice way...
> Thanks|||Simon,
I would
1. lock the table.
2. insert getdate() into some other table, say replication_batch
3. release the lock
4. copy the rows changed between the last 2 values in replication_batch
Good luck!|||If you are concerned with missing updates due to timing issues, and if you
ALWAYS want to overwrite the data, just change your date to the start of the
last run, rather than the end of it (as I think Omni suggested). Yes, you
will copy over more rows than is needed, but you will have the accuracy you
are looking for. Although replication is likely a much simpler, and
dependable solution.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1147882907.441123.246700@.38g2000cwa.googlegroups.com...
> Simon,
> I would
> 1. lock the table.
> 2. insert getdate() into some other table, say replication_batch
> 3. release the lock
> 4. copy the rows changed between the last 2 values in replication_batch
> Good luck!
>