Users Getting Same Ticket - ADO/SQL server 7

With a SQL statement, we are getting the next most important ticket out of the database and marking it with the UserId of the person who hit "Get Next Ticket" button. The SQL string that does this is Connection.Executed. The result should be that users *cannot* grab a ticket that they did not mark so that we don't have people working on the same tickets. Once the user is done with the ticket, and the "Update" button is pressed, changes/updates are made to the ticket and his/her UserId is removed from the ticket.

The definition of the executed SQL string variable fwithin VB follows:

/*SNIP
strReserveNextTicket = "UPDATE T_TICKET" & _
" SET researcher = '" & strUserID & "'" & _
" FROM" & _
" (SELECT TOP 1 T_TICKET.ticket_number, T_TICKET.sequence_number, T_TICKET.transmit_dt" & _
" FROM T_TICKET INNER JOIN" & _
" T_WORKFLOW_STATUS ON" & _
" T_TICKET.workflow_status_id = T_WORKFLOW_STATUS.workflow_status_id" & _
" Inner Join" & _
" T_USER_WKFLW ON" & _
" T_WORKFLOW_STATUS.workflow_status_id = T_USER_WKFLW.workflow_status_id" & _
" WHERE (recipient_type_cd = '" & strUserType & "' OR" & _
" recipient_type_cd = 'RES') AND (researcher is null)" & _
" ORDER BY type, transmit_dt) as TOPTICKET" & _
" WHERE T_TICKET.ticket_number = TOPTICKET.ticket_number AND" & _
" T_TICKET.sequence_number = TOPTICKET.sequence_number AND" & _
" T_TICKET.transmit_dt = TOPTICKET.transmit_dt"
/*SNIP

cursorlocation = adUseClient (should this be server side?)
cnULTRATixNum.Execute strReserveNextTicket

PROBLEM:
Users are somehow still getting the same ticket.

Is there a problem in the logic there? Should this logic (assuming it is not flawed) be rewritten into a stored procedure? How does SQL Server 7.0 process this statement...does it break it down into pieces as opposed to a single executed action so that this problem of 2 people getting the same ticket can occur?

We are trying to essentially mark the ticket a user gets from the database so that no one else will access it. Because of the nature of the work being done, it is possible for a user to download the latest greatest ticket and not perform edits on it for several minutes.

Any suggestions about our current logic or a different approach are appreciated.

Thanks,
Jason

First
1. Separate your innerselect from the update

i.e get an updatable recordset with your select
*** use locktype of adLockBatchOptimistic...

then prior to doing the update, resync with the database

with recordset
.resync
end with


and then prior to update do


have not tried it... but let me know

Thanks

"and then prior to update do


have not tried it... but let me know "


Is there supposed to be some text between "do" and "have not tried..."? I'm on the edge of my seat :)


Jason

sorry no code is supposed to be there

I left for a little while
ddidn't get this msg until now

That is the way we were doing it to begin with and it didn't work. We thought that since there was time, however small it was, between fetching, marking, and updating the record that it was allowing users to grab the same ticket. The thought was that if we did the retrieve and marking in the same step, we'd eliminate that possibility.

Thanks for the info though!

Any other suggestions out there?

Jason

Shameless bump here...

Could it be that in our development environment that had SQL 7 on a single processor server that executed requests/processes consecutively as they came in but the multiprocessor database cluster used in the production environment is executing processes in parallel, thereby producing conflict?

Or secondly, could there be some functionality or permissions toggled in the production environment SQL 7 installation that wasn't toggled in the development environment? I ask this because all of the "solutions" we have produced to fix this problem *worked* in the (single processor) development environment, only to fall flat in the production environment.

Any suggestions or help sincerely appreciated,

Jason

Instead of using a join, have you attempted to create a view?