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)?

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!ecngs!feeder2.ecngs.de!feeder.erje.net!diablo2.news.osn.de!news.osn.de!diablo2.news.osn.de!news.belwue.de!rz.uni-karlsruhe.de!feed.news.schlund.de!schlund.de!news.online.de!not-for-mail
From	"Mark Schneider" <Mark.Schneider@dexag.at>
Newsgroups	comp.databases.ms-sqlserver
Subject	stored procedure calling sub-procedure with updlock select. How could I release the updatelog if sub-proc is finished (calling proc still running)?
Date	Wed, 27 Jul 2011 17:23:17 +0200
Organization	1&1 Internet AG
Lines	36
Message-ID	<j0pahg$8h7$1@online.de> (permalink)
NNTP-Posting-Host	p4fe404b5.dip0.t-ipconnect.de
X-Trace	online.de 1311780208 8743 79.228.4.181 (27 Jul 2011 15:23:28 GMT)
X-Complaints-To	abuse@einsundeins.com
NNTP-Posting-Date	Wed, 27 Jul 2011 15:23:28 +0000 (UTC)
X-Priority	3
X-MSMail-Priority	Normal
X-Newsreader	Microsoft Outlook Express 6.00.2900.5931
X-RFC2646	Format=Flowed; Original
X-MimeOLE	Produced By Microsoft MimeOLE V6.00.2900.6109
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:572

Show key headers only | 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