>-----Original Message-----
>How about defining a column of data type TimeStamp,
retrieve the TimeStamp
>value at at the time you select your record prior to the
update. For the
>Update statement, specify WHERE TimeStampColumn =
SavedTSValue. The only way
>this value will be equivelent is if the row were not
updated since your
>retrieval.
>
>Steve
>"David" <anonymous@discussions.microsoft.com> wrote in
message
>news:2184101c45ac8$56276d40$a401280a@phx.gbl...
>> Thanks for this Mary. I implemented your suggestion but
>> unfortunately I've still got a problem and multiple
users
>> can still get the same record.
>> I think it is if 2 or more users run the stored
procedure
>> at the same / almost the same time they both manage to
get
>> the same @CSIDToLock (from my Stored Proc at the bottom
of
>> this post) from the Select statement, each before the
>> other has run the update query. They both then run the
>> update query and both manage to update the single row so
>> @@RowCount = 1. The problem of course being that the
>> second person to do it overwrites the first persons
>> change. Is there a way to ensure that the 2 actions
>> (select and then update) run without anyone else being
>> able to run them at the same time?
>>
>> David
>>
>> >-----Original Message-----
>> >What you want to do is to re-write your stored
procedure,
>> creating an
>> >explicit transaction (see "Explicit Transactions" and
>> related topics
>> >in SQL BOL). Inside the transaction you'll select the
>> record to call ,
>> >update the locking field, and store the record ID in a
>> local variable,
>> >all as one unit of work. If the update fails, then
>> someone else got
>> >there first, so you code the logic (see @@rowcount) to
>> rollback and
>> >try again. This way you won't get duplicates returned
to
>> simultaneous
>> >callers.
>> >
>> >--Mary
>> >
>> >On Thu, 24 Jun 2004 08:43:52 -0700, "David"
>> ><anonymous@discussions.microsoft.com> wrote:
>> >
>> >>Hi All,
>> >>I've a problem that requires a fairly lengthy
>> introduction
>> >>by me. If you've the time and the willpower I'd
really
>> >>appreciate your help.
>> >>I'm running a application based on Access 2000 and SQL
>> >>Server 2000.
>> >>The system is designed to allocate a single record to
a
>> >>callcentre agent based on it being the most relevant
>> >>record to call at the time (designated next call time,
>> >>previous result etc.
>> >>The agents Access db uses a pass-through query to run
a
>> >>stored procedure with a variable of the agent's ID.
>> >>There stored procedure uses a query to get a single
>> record
>> >>to call and then updates a locking field within that
>> >>record with the agent ID. The query will exclude any
>> >>records already containing an agent ID. The stored
>> >>procedure then passes the unique ID of the record back
>> to
>> >>the Access db. Access then uses find first to go to
the
>> >>correct record.
>> >>When the agent finishes with that record it sets the
>> >>locking field back to 0 and the process starts again!
>> >>The problem is that if several agents request a record
>> at
>> >>the same time the SQL profiler shows different
requests
>> >>being run for each agent, but they get the same unique
>> ID
>> >>passed back to all of them, and the record is locked
>> with
>> >>the agent ID of the first agent to make the request.
>> The
>> >>agents will all get the same record on screen and
>> attempt
>> >>to call the same company, which is obviously not
ideal!
>> >>
>> >>Thanks in advance
>> >>
>> >>p.s In case it helps, here is the stored procedure I
run:
>> >>CREATE PROC spLockNewRec
>> >> @PHIDToLock smallint
>> >>AS
>> >>
>> >>DECLARE @CSIDToLock int
>> >>
>> >>BEGIN
>> >>UPDATE tblClientFile SET tblClientFile.intPHIDLock = 0
>> >>WHERE tblClientFile.intPHIDLock = @PHIDToLock
>> >>END
>> >>
>> >>SET rowcount 1
>> >>
>> >>BEGIN TRANSACTION
>> >>
>> >>SELECT @CSIDToLock = dbo.tblClientFile.CSID
>> >>FROM dbo.tblDecisionMakers RIGHT OUTER JOIN
>> >> dbo.tblClientFile ON
>> >>dbo.tblDecisionMakers.CSID = dbo.tblClientFile.CSID
LEFT
>> >>OUTER JOIN
>> >> dbo.tblCallResult ON
>> >>dbo.tblDecisionMakers.CRID = dbo.tblCallResult.CRID
>> >>WHERE (dbo.tblDecisionMakers.dtmNextCallTime <=
>> GETDATE
>> >>()) AND (dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>> >>(dbo.tblCallResult.intCallbackType = 9) OR
>> >>
dbo.tblCallResult.intCallbackType
>> IS
>> >>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL)) OR
>> >>
>> >>(dbo.tblDecisionMakers.dtmNextCallTime IS NULL) AND
>> >>(dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>> >>(dbo.tblCallResult.intCallbackType = 9) OR
>> >>
dbo.tblCallResult.intCallbackType
>> IS
>> >>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL))
>> >>ORDER BY dbo.tblCallResult.intCallPriority,
>> >>dbo.tblDecisionMakers.dtmNextCallTime
>> >>
>> >>UPDATE tblClientFile
>> >>SET intPHIDLock = @PHIDToLock, dtmPHIDLock = getdate()
>> >>where tblClientFile.CSID = @CSIDToLock
>> >>
>> >>COMMIT TRANSACTION
>> >>
>> >>SELECT @CSIDToLock
>> >>GO
>> >
>> >.
>> >
>
>
>.