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" 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: 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 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