Re: Stumped - Level 16 error terminates stored proc

From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 10:11:09 -0400

Day,

This seems to describe your problem. The manual's behavior is not met
without this fix. (The fix claims to be for all levels of SQL Server 2000,
but was last reviewed in October 2003.)

FIX: LOCK_TIMEOUT Causes Transaction to Roll Back and @@error Does Not
Capture Error 1222
http://support.microsoft.com/default.aspx?scid=kb;en-us;286286

Russell Fields

"Day Late" <Day Late@discussions.microsoft.com> wrote in message
news:E28F7A19-AEE7-40CD-A061-D1AE4A3D8B2A@microsoft.com...
> I have supplied a section of the code that is causing problems. The first
section is the code, the second is the output from the run. My question is
why is it terminating the stored procedure and not allowing me to handle the
error? I'm stumped. A Level 16 is suppose to be a recoverable level.
>
> .......... Other sql code
> Print @SQL
> SET @Transaction_SQL_UPD_Sequel = @SQL
> SET @nbr_times_to_retry_prev_lock = 5
> SET LOCK_TIMEOUT 200
> Print '12.2'
> EXECUTE (@Transaction_SQL_UPD_Sequel)
> Set @ERR_Number = @@ERROR
> Print '12.3'
> IF @ERR_Number = 1222 and @error_counter < @nbr_times_to_retry_prev_lock
> BEGIN
> ....
>
> -- the output from code above; notice that the statement the prints 12.3
is never reached. Other note: Why a execute(@sql) technique? Because this is
a common module where the sql is passed in to this sp that is designed to
capture any errors and retry attempts against locked rows.
>
> UPDATE DSA.dbo.Inventory WITH(ROWLOCK) SET inv_mapping_status = 'EDS In
Process', inv_mapping_status_date = GETDATE(), Last_Update_Date = GETDATE(),
Last_Updated_By_Text = 'MBLT_sp_upd_inv_status' FROM DSA.dbo.Inventory INV_2
WITH(NOLOCK) where INV_2.inventory_id = (SELECT TOP 1 temp.inventory_id FROM
#temp_inv_status as temp where temp.inventory_id > 162984 order by
temp.inventory_id)
> 12.2
> Server: Msg 1222, Level 16, State 54, Line 1
> Lock request time out period exceeded.
> The statement has been terminated.
>
> Thanks for the help, I clearly I'm not understanding something and I'm not
a newbie.
>