"C-W" <nomailplease@microsoft.nospam> wrote in message
news:%23pODpRkfFHA.2512@TK2MSFTNGP14.phx.gbl...
> I was wondering if something along these lines is possible in SQL
Server...
>
> We have a table which contains a particular record.  This table also has
> lots of related tables.  I would like to take a snapshot of a particular
> record and all it's sub table entries.  The user can then make changes to
> the data which requires control being passed back to the user.  The user
> then decides to save the changes made or decides to abort them and
rollback
> to the snapshot.  Other users need to be able to query both the snapshot
and
> the current state of the record.
>
> Is anything like this possible?  I am presuming you can't use transactions
> for due to the fact that the user could take minutes to make their
changes.
> Anything similar to this possible?
>
> Thanks in advance.
>
>

"JT" <some***@microsoft.com> wrote in message
news:%23GIKqZkfFHA.1204@TK2MSFTNGP12.phx.gbl...
> If you actually want other users to query results based on one user's
> currently uncommitted data changes (rather than being blocked), then read
> up
> on the various options for setting the transaction isolation level. In
> particular "set transaction isolation level read uncommitted". Although
> you
> can technically get this to work the way you want, you may want to think
> this through from a process workflow angle.
>

"C-W" <nomailplease@microsoft.nospam> wrote in message
news:%23KG3E9kfFHA.2824@TK2MSFTNGP10.phx.gbl...
> Thanks,
>
>
>
> I don't think this will work for me.  As a second user, I need to be able
to
> view the before and after state of the data.  I need do to this to find
the
> most pessimistic value from the two sets of data so that the second user
> can't insert something that would be invalid if the first user decided to
> rollback their changes.  E.g. I have 4 items in stock - the first user
> changes their order quantity from 3 down to 2 leaving just 2 in stock.  I
> don't want to allow the second user to be able to order these 2 item
because
> if the first user cancelled their amendment that you leave -1 in stock.
It
> also works the other way around (e.g. the first user want to add more onto
> their order instead).
>
>
>
>
>
>
>
>
> "JT" <some***@microsoft.com> wrote in message
> news:%23GIKqZkfFHA.1204@TK2MSFTNGP12.phx.gbl...
> > If you actually want other users to query results based on one user's
> > currently uncommitted data changes (rather than being blocked), then
read
> > up
> > on the various options for setting the transaction isolation level. In
> > particular "set transaction isolation level read uncommitted". Although
> > you
> > can technically get this to work the way you want, you may want to think
> > this through from a process workflow angle.
> >
>
>

"JT" <some***@microsoft.com> wrote in message
news:uKxSeWlfFHA.912@TK2MSFTNGP10.phx.gbl...
> It sounds like this is a web applications and the users may spend a few
> minutes glossing over the order entry form before finally submitting it
> (or
> just closing their browser and cancelling the whole thing). When the user
> submit, you can perform a quick validation query just prior to processing
> the order. Also, you can define a constraint on the inventory table that
> block any attempt to decrement the stock count to less than 0 and just
> accept the fact that there will be occasional orders that are aborted at
> the
> last moment due to contention with other users.
>
> "C-W" <nomailplease@microsoft.nospam> wrote in message
> news:%23KG3E9kfFHA.2824@TK2MSFTNGP10.phx.gbl...
>> Thanks,
>>

"C-W" <nomailplease@microsoft.nospam> wrote in message
news:OJ$MOylfFHA.3612@TK2MSFTNGP12.phx.gbl...
> We are going to have both a back office system and a web application.
> Taking the web application approach, think of a site like Brisish Airways
> (just as an example) - if you select some seats on a plane we want to
> reserve these seats as soon as you've selected them and clicked a confirm
> button.  You then go onto extra screens where you've got to enter contact
> details and credit card details etc.  If you close the browser, sure your
> seats will be made available again but if you click on the final submit
> button we need to guarantee that the seats will be available.  Not sure if
> BA do anything like this as airlines often over book, but it's the best
> example I could think of.
>
>
> "JT" <some***@microsoft.com> wrote in message
> news:uKxSeWlfFHA.912@TK2MSFTNGP10.phx.gbl...
> > It sounds like this is a web applications and the users may spend a few
> > minutes glossing over the order entry form before finally submitting it
> > (or
> > just closing their browser and cancelling the whole thing). When the
user
> > submit, you can perform a quick validation query just prior to
processing
> > the order. Also, you can define a constraint on the inventory table that
> > block any attempt to decrement the stock count to less than 0 and just
> > accept the fact that there will be occasional orders that are aborted at
> > the
> > last moment due to contention with other users.
> >
> > "C-W" <nomailplease@microsoft.nospam> wrote in message
> > news:%23KG3E9kfFHA.2824@TK2MSFTNGP10.phx.gbl...
> >> Thanks,
> >>
>
>

"JT" <some***@microsoft.com> wrote in message
news:uRi$FVmfFHA.3304@TK2MSFTNGP12.phx.gbl...
> Perhaps you need to introduce a concept where a reservation can be
> submitted
> and pending but not yet confirmed. At the point when the user selects a
> seat
> and clicks the submit button, a reservation record can be inserted with
> the
> date/time stored in a DTPending column, and the seat is unavailable for
> further reservations. Only after the additional information has been
> submitted and validated, is the reservation flagged as confirmed. You can
> have a scheduled SQL Server job that queries for any reservations that
> have
> been pending but not yet confirmed for more than say 20 minutes. These
> reservations are then flagged as cancelled, thus making the seats made
> available again, and perhaps an email notification sent to the user
> stating
> that their reservation was cancelled due to lack of confirmation. This is
> a
> more deterministic method than holding the reservations in an uncommitted
> state within a transaction or snapshotting them into temporary records
> that
> must then be purged and reconciled.

"C-W" <nomailplease@microsoft.nospam> wrote in message
news:%23pODpRkfFHA.2512@TK2MSFTNGP14.phx.gbl...
>I was wondering if something along these lines is possible in SQL Server...
>
> We have a table which contains a particular record.  This table also has
> lots of related tables.  I would like to take a snapshot of a particular
> record and all it's sub table entries.  The user can then make changes to
> the data which requires control being passed back to the user.  The user
> then decides to save the changes made or decides to abort them and
> rollback to the snapshot.  Other users need to be able to query both the
> snapshot and the current state of the record.
>
> Is anything like this possible?  I am presuming you can't use transactions
> for due to the fact that the user could take minutes to make their
> changes. Anything similar to this possible?
>
> Thanks in advance.
>
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:%23pvo3imfFHA.3124@TK2MSFTNGP12.phx.gbl...
> SQL 2000 does not have that ability but 2005 certainly does.  You can
> achieve this in 2005 via the snapshot isolation level or the DB Snapshots.
>
> --
> Andrew J. Kelly  SQL MVP