Re: Access 2000 & Stored Procedures

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 10:34:58 -0700

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

Relevant Pages

  • Stored procedure output
    ... How do I "capture" the output of a stored procedure if it is run by SQL ... agent. ... Basically I want to redirect the result set returned into a text file to ftp ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure doesnt update records
    ... >It's funny that the first change in the tables is done, ... >However the next day when the stored procedure is ran from the program, ... >Is there any cache built for the tables in SQL that is not refreshed? ... updating until that transaction is closed. ...
    (borland.public.delphi.database.ado)
  • Transaction im SqlCommand und in der Stored Procedure
    ... wenn ich einem SqlCommand eine Transaction zuweise und der SqlCommand eine ... Stored Procedure aufruft, die ebenfalls eine Transaction öffnet (BEGIN ... was macht der Sql Server dann? ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: ADO.net or TSQL Transactions
    ... A transaction has to be ATOMIC regardless of who starts it otherwise what ... Call a single>> stored procedure that executes the transactions and returns ... See the BEGIN>> TRANSACTION and related topics in SQL Books Online for more>> information. ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO.net or TSQL Transactions
    ... A transaction has to be ATOMIC regardless of who starts it otherwise what ... Call a single>> stored procedure that executes the transactions and returns ... See the BEGIN>> TRANSACTION and related topics in SQL Books Online for more>> information. ...
    (microsoft.public.dotnet.framework.adonet)