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

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

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

On Wednesday, 29 November 2017 15:44:18 UTC, Anton Shepelev  wrote:
> Erland Sommarskog to Anton Shepelev:
> 
> >>What  about  error  handling?  In the case of pure
> >>nesting  I  could   simply   ensure   that   every
> >>BEGIN TRAN has a corresponding COMMIT or ROLLBACK,
> >>probably with the help of a TRY..CATCH block.   In
> >>the current model, however, it is a tad less triv-
> >>ial,  because  I  must  test  @@TRANCOUNT  to  see
> >>whether  a nested call did not roll back my trans-
> >>action in the course of  its  own  error  handling
> >>logic.
> >
> >If  you  don't  want  to use TRY-CATCH, you need to
> >write some tedious piece of code.
> 
> The hay surely smelleth of that weed.  I was  think-
> ing along the lines of:
> 
>    https://technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
> 
> Or did you mean something else?
> 
> >On my web site there is a series of articles on er-
> >ror and transaction handling in SQL  Server,  which
> >starts here:
> >http://www.sommarskog.se/error_handling/Part1.html
> 
> Thank you, I have started to read it and enjoy arti-
> cle very much.  Kudos for a clean website.

Lately, I've worked mainly on regularly built data warehousing,
i.e. just copying data in order to make reports.  So error
handling is less about transactions and TRY-CATCH and more
"if it doesn't tun, maybe it will tomorrow".

I used to use an error handling block that goes like:

BEGIN TRANSACTION 
EXEC procedure
SET @returnerror = @@ERROR; IF (@returnerror <> 0 ) GOTO failure
....
COMMIT TRANSACTION
GOTO terminate
failure:
IF ( @@TRANCOUNT > 0 ) BEGIN ROLLBACK TRANSACTION END
RAISERROR(N'Error.', 16, 1)
terminate:
/* End of procedure */

There was also something about deallocating a cursor, but then I dug 
cursor variables that eliminate themselves (close enough).

The approach depends on exiting a procedure with an error that
can be read where the procedure was called, and then raising 
another error at the end of this procedure, to hand it on.
But only "GOTO failure" needs to be included after each place
where an error may need to be handled in the program body.

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

Thread

Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 14:56 +0300
  Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 05:37 -0800
    Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 18:24 +0300
      Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 08:36 -0800
        Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 21:13 +0300
          Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 16:21 -0800
      Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-25 21:36 +0100
        Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-26 19:43 +0300
          Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-26 19:55 +0100
            Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-27 11:34 +0300
              Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-27 11:56 +0100
                Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-27 14:23 -0800
                Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-28 08:50 +0100
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:11 +0300
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:22 +0300
                Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-29 13:44 +0100
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 18:44 +0300
                Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 19:42 +0300
                Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-30 13:19 -0800

csiph-web