Access 2000 & Stored Procedures

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
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
|||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[vbcol=seagreen]
>callers.
>--Mary
>On Thu, 24 Jun 2004 08:43:52 -0700, "David"
><anonymous@.discussions.microsoft.com> wrote:
introduction[vbcol=seagreen]
record[vbcol=seagreen]
to[vbcol=seagreen]
at[vbcol=seagreen]
ID[vbcol=seagreen]
with[vbcol=seagreen]
The[vbcol=seagreen]
attempt[vbcol=seagreen]
GETDATE[vbcol=seagreen]
IS[vbcol=seagreen]
IS
>.
>
|||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...[vbcol=seagreen]
> 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
> creating an
> related topics
> record to call ,
> local variable,
> someone else got
> rollback and
> simultaneous
> introduction
> record
> to
> at
> ID
> with
> The
> attempt
> GETDATE
> IS
> IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>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
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>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
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.

>--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[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.

>--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[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>