Stumped - Level 16 error terminates stored proc

From: Day Late (Late_at_discussions.microsoft.com)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 06:34:02 -0700

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.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... explicitly code dummy words as bind variable placekeepers, put the host variables on the ...
    (comp.lang.cobol)
  • Re: UPDATE query in Access 2003 raising error
    ... string into a variable strSQL but create the qrydef from a string strSQLx (I ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: Poor performance when executing stored procedure
    ... > Generally I would write stored procedures to do only one job. ... If SQL Server ... > which is even worse those sps can execute each other. ... > then executed spOrders which is executed in spCustomers and then got stuck ...
    (microsoft.public.sqlserver.programming)