Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #572

stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)?

Show all headers | View raw

Hi,

-> Description (short): <------------------
stored procedure calling sub-procedure with updlock select. How could I 
release the updatelog if sub-proc is finished (calling proc still running)?
MS SQL Server 2000.

-> Description (longer): <------------------
I have a stored procedure (procA) for statistics that are summarized in a 
table (tableA).
It's calling a sub-procedure (procB, via execute)  to get a key token (ID) 
from another table (tableB).

The file structure, fields or logic of table B cannot be modified by me.
It's populated by a SAP system.

- procA is running about 20 minutes (it's ok, millions of rows and many 
statistics)
- procB uses an updlock to retrieve a key token and increment it by 1 
(selecting and updating tableB)

-> Problem(s): <------------------
As long as procA is running no other user/process can access procB to 
retrieve the next free key token and increment it.
Under no circumstances two users should ever get the same token.

-> Looking for: <------------------
A solution to release the update lock of the sub-procedure while the caller 
is still running?

How would I go about that?

Mark

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? "Mark Schneider" <Mark.Schneider@dexag.at> - 2011-07-27 17:23 +0200
  Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-28 00:03 +0200
    Re: stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)? "Mark Schneider" <Mark.Schneider@dexag.at> - 2011-08-02 07:23 +0200

csiph-web