rollback tran

rollback tran

Post by Andr » Sun, 23 Sep 2001 15:48:55



i have a sproc that inserts data into 3 tables.  let's say the sproc barfs
on the 2nd insert.  i'm confused as to why i'm left with data in the first
table, as a result of the 1st insert, even though i've told it to rollback.

with what occurs when i run this and i get an error in the results window of
query analyzer, instead of the correct message "insert failed" that my sproc
is supposed to return.  if for some reason the 2nd insert isn't able to run,
i'll get a message in the results window, instead of my code handling it.

my code - in simplified form - is following.  any insight would be
apprciated.

thanks, Andre

ALTER  PROCEDURE usp_insert_user






as
set nocount on


/*
  first insert the new user
*/
 begin tran

  begin
   insert  tbl_user
    (user_name,
     password,
     first_name,
     last_name,
     last_modified)
   select




     getdate()


   goto ErrorHandler

   -- get the id of the user just inserted

  end

  /*
    then insert the site(s) the user has access to
  */
  begin

   insert tbl_user_site
    (user_id,
     site)


   from #temp_site


   goto ErrorHandler

  end

  /*
    then insert the report(s) the user has access to
  */
  begin

   insert tbl_user_report
    (user_id,
     report_id)


   from #temp_report

  end

 commit tran
  select 'insert successful' as result
  return 0

ErrorHandler:
 begin
  Raiserror ('Insert Failed', 16, 1)
  rollback tran
  return 1
 end

 
 
 

rollback tran

Post by Andrew J. Kell » Sun, 23 Sep 2001 21:59:17


My guess is that you still have an open tran and the rollback never
occurred. If the error was severe enough on the 2nd table insert then it

Rollback code never gets executed. Your error routine should have the
Rollback before the RaiseError.

--
Andrew J. Kelly,   SQL Server MVP


> i have a sproc that inserts data into 3 tables.  let's say the sproc barfs
> on the 2nd insert.  i'm confused as to why i'm left with data in the first
> table, as a result of the 1st insert, even though i've told it to
rollback.

consistent
> with what occurs when i run this and i get an error in the results window
of
> query analyzer, instead of the correct message "insert failed" that my
sproc
> is supposed to return.  if for some reason the 2nd insert isn't able to
run,
> i'll get a message in the results window, instead of my code handling it.

> my code - in simplified form - is following.  any insight would be
> apprciated.

> thanks, Andre

> ALTER  PROCEDURE usp_insert_user






> as
> set nocount on


> /*
>   first insert the new user
> */
>  begin tran

>   begin
>    insert  tbl_user
>     (user_name,
>      password,
>      first_name,
>      last_name,
>      last_modified)
>    select




>      getdate()


>    goto ErrorHandler

>    -- get the id of the user just inserted

>   end

>   /*
>     then insert the site(s) the user has access to
>   */
>   begin

>    insert tbl_user_site
>     (user_id,
>      site)


>    from #temp_site


>    goto ErrorHandler

>   end

>   /*
>     then insert the report(s) the user has access to
>   */
>   begin

>    insert tbl_user_report
>     (user_id,
>      report_id)


>    from #temp_report

>   end

>  commit tran
>   select 'insert successful' as result
>   return 0

> ErrorHandler:
>  begin
>   Raiserror ('Insert Failed', 16, 1)
>   rollback tran
>   return 1
>  end


 
 
 

rollback tran

Post by Ron Talmag » Sun, 23 Sep 2001 16:05:28


Andre,



HTH,
Ron
--
Ron Talmage
SQL Server MVP


> i have a sproc that inserts data into 3 tables.  let's say the sproc barfs
> on the 2nd insert.  i'm confused as to why i'm left with data in the first
> table, as a result of the 1st insert, even though i've told it to
rollback.

consistent
> with what occurs when i run this and i get an error in the results window
of
> query analyzer, instead of the correct message "insert failed" that my
sproc
> is supposed to return.  if for some reason the 2nd insert isn't able to
run,
> i'll get a message in the results window, instead of my code handling it.

> my code - in simplified form - is following.  any insight would be
> apprciated.

> thanks, Andre

> ALTER  PROCEDURE usp_insert_user






> as
> set nocount on


> /*
>   first insert the new user
> */
>  begin tran

>   begin
>    insert  tbl_user
>     (user_name,
>      password,
>      first_name,
>      last_name,
>      last_modified)
>    select




>      getdate()


>    goto ErrorHandler

>    -- get the id of the user just inserted

>   end

>   /*
>     then insert the site(s) the user has access to
>   */
>   begin

>    insert tbl_user_site
>     (user_id,
>      site)


>    from #temp_site


>    goto ErrorHandler

>   end

>   /*
>     then insert the report(s) the user has access to
>   */
>   begin

>    insert tbl_user_report
>     (user_id,
>      report_id)


>    from #temp_report

>   end

>  commit tran
>   select 'insert successful' as result
>   return 0

> ErrorHandler:
>  begin
>   Raiserror ('Insert Failed', 16, 1)
>   rollback tran
>   return 1
>  end

 
 
 

rollback tran

Post by Andr » Mon, 24 Sep 2001 13:08:11


Ron,

The select statement is used to provide data for the insert.  Do you mean to

work for me?

Andre


> Andre,



> HTH,
> Ron
> --
> Ron Talmage
> SQL Server MVP



> > i have a sproc that inserts data into 3 tables.  let's say the sproc
barfs
> > on the 2nd insert.  i'm confused as to why i'm left with data in the
first
> > table, as a result of the 1st insert, even though i've told it to
> rollback.

> consistent
> > with what occurs when i run this and i get an error in the results
window
> of
> > query analyzer, instead of the correct message "insert failed" that my
> sproc
> > is supposed to return.  if for some reason the 2nd insert isn't able to
> run,
> > i'll get a message in the results window, instead of my code handling
it.

> > my code - in simplified form - is following.  any insight would be
> > apprciated.

> > thanks, Andre

> > ALTER  PROCEDURE usp_insert_user






> > as
> > set nocount on


> > /*
> >   first insert the new user
> > */
> >  begin tran

> >   begin
> >    insert  tbl_user
> >     (user_name,
> >      password,
> >      first_name,
> >      last_name,
> >      last_modified)
> >    select




> >      getdate()


> >    goto ErrorHandler

> >    -- get the id of the user just inserted

> >   end

> >   /*
> >     then insert the site(s) the user has access to
> >   */
> >   begin

> >    insert tbl_user_site
> >     (user_id,
> >      site)


> >    from #temp_site


> >    goto ErrorHandler

> >   end

> >   /*
> >     then insert the report(s) the user has access to
> >   */
> >   begin

> >    insert tbl_user_report
> >     (user_id,
> >      report_id)


> >    from #temp_report

> >   end

> >  commit tran
> >   select 'insert successful' as result
> >   return 0

> > ErrorHandler:
> >  begin
> >   Raiserror ('Insert Failed', 16, 1)
> >   rollback tran
> >   return 1
> >  end

 
 
 

rollback tran

Post by Tibor Karasz » Tue, 25 Sep 2001 19:00:00


> The select statement is used to provide data for the insert.  Do you mean to

> work for me?

I'm sure that Ron quite simply missed that :-).

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Ron,

> The select statement is used to provide data for the insert.  Do you mean to

> work for me?

> Andre



> > Andre,



> > HTH,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP



> > > i have a sproc that inserts data into 3 tables.  let's say the sproc
> barfs
> > > on the 2nd insert.  i'm confused as to why i'm left with data in the
> first
> > > table, as a result of the 1st insert, even though i've told it to
> > rollback.

> > consistent
> > > with what occurs when i run this and i get an error in the results
> window
> > of
> > > query analyzer, instead of the correct message "insert failed" that my
> > sproc
> > > is supposed to return.  if for some reason the 2nd insert isn't able to
> > run,
> > > i'll get a message in the results window, instead of my code handling
> it.

> > > my code - in simplified form - is following.  any insight would be
> > > apprciated.

> > > thanks, Andre

> > > ALTER  PROCEDURE usp_insert_user






> > > as
> > > set nocount on


> > > /*
> > >   first insert the new user
> > > */
> > >  begin tran

> > >   begin
> > >    insert  tbl_user
> > >     (user_name,
> > >      password,
> > >      first_name,
> > >      last_name,
> > >      last_modified)
> > >    select




> > >      getdate()


> > >    goto ErrorHandler

> > >    -- get the id of the user just inserted

> > >   end

> > >   /*
> > >     then insert the site(s) the user has access to
> > >   */
> > >   begin

> > >    insert tbl_user_site
> > >     (user_id,
> > >      site)


> > >    from #temp_site


> > >    goto ErrorHandler

> > >   end

> > >   /*
> > >     then insert the report(s) the user has access to
> > >   */
> > >   begin

> > >    insert tbl_user_report
> > >     (user_id,
> > >      report_id)


> > >    from #temp_report

> > >   end

> > >  commit tran
> > >   select 'insert successful' as result
> > >   return 0

> > > ErrorHandler:
> > >  begin
> > >   Raiserror ('Insert Failed', 16, 1)
> > >   rollback tran
> > >   return 1
> > >  end

 
 
 

rollback tran

Post by Ron Talmag » Wed, 26 Sep 2001 01:51:26


Yes, I missed that.

Ron



> > The select statement is used to provide data for the insert.  Do you
mean to

ever
> > work for me?

> I'm sure that Ron quite simply missed that :-).

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




> > Ron,

> > The select statement is used to provide data for the insert.  Do you
mean to

ever
> > work for me?

> > Andre



> > > Andre,


reset to

> > > HTH,
> > > Ron
> > > --
> > > Ron Talmage
> > > SQL Server MVP



> > > > i have a sproc that inserts data into 3 tables.  let's say the sproc
> > barfs
> > > > on the 2nd insert.  i'm confused as to why i'm left with data in the
> > first
> > > > table, as a result of the 1st insert, even though i've told it to
> > > rollback.

> > > consistent
> > > > with what occurs when i run this and i get an error in the results
> > window
> > > of
> > > > query analyzer, instead of the correct message "insert failed" that
my
> > > sproc
> > > > is supposed to return.  if for some reason the 2nd insert isn't able
to
> > > run,
> > > > i'll get a message in the results window, instead of my code
handling
> > it.

> > > > my code - in simplified form - is following.  any insight would be
> > > > apprciated.

> > > > thanks, Andre

> > > > ALTER  PROCEDURE usp_insert_user






> > > > as
> > > > set nocount on


> > > > /*
> > > >   first insert the new user
> > > > */
> > > >  begin tran

> > > >   begin
> > > >    insert  tbl_user
> > > >     (user_name,
> > > >      password,
> > > >      first_name,
> > > >      last_name,
> > > >      last_modified)
> > > >    select




> > > >      getdate()


> > > >    goto ErrorHandler

> > > >    -- get the id of the user just inserted

> > > >   end

> > > >   /*
> > > >     then insert the site(s) the user has access to
> > > >   */
> > > >   begin

> > > >    insert tbl_user_site
> > > >     (user_id,
> > > >      site)


> > > >    from #temp_site


> > > >    goto ErrorHandler

> > > >   end

> > > >   /*
> > > >     then insert the report(s) the user has access to
> > > >   */
> > > >   begin

> > > >    insert tbl_user_report
> > > >     (user_id,
> > > >      report_id)


> > > >    from #temp_report

> > > >   end

> > > >  commit tran
> > > >   select 'insert successful' as result
> > > >   return 0

> > > > ErrorHandler:
> > > >  begin
> > > >   Raiserror ('Insert Failed', 16, 1)
> > > >   rollback tran
> > > >   return 1
> > > >  end

 
 
 

rollback tran

Post by Andr » Wed, 26 Sep 2001 02:47:41


Andrew,

Thanks for the reply.  I'm not sure how i would put in a rollback before the
raiserror.  wouldn't that mean that i'd never commit the transaction?
you're also correct about the error being severe enough to halt the sp.  for
instance, if you look at this snipit of my sproc below, which i've doctored
to die and product this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'user_name', table
'hcp.dbo.tbl_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg 50000, Level 16, State 1, Line 29
Insert Failed

How can i trap for this error so it dies gracefully, instead of producing
the above error?

thanks, Andre

begin tran

 begin
  insert  tbl_user
   (user_name,
    password,
    first_name,
    last_name,
    last_modified)
  select
    null,
    1,
    'a',
    'a',
    getdate()


  goto ErrorHandler

 end

commit tran
 select 'insert successful' as result

ErrorHandler:
 begin
  Raiserror ('Insert Failed', 16, 1)
  rollback tran
 end

 
 
 

rollback tran

Post by Andr » Wed, 26 Sep 2001 02:34:25


Andrew,

How would I go about putting the rollback before the raiserror?  You are
right about the error being severe enough and halting the sp.  Using this
example below, this insert would produce the following error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'user_name', table
'hcp.dbo.tbl_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg 50000, Level 16, State 1, Line 29
Insert Failed

What I'd like to be able to do is have some error coding that would trap for
this error.  How can I do that?

Thanks again, Andre

begin tran

 begin
  insert  tbl_user
   (user_name,
    password,
    first_name,
    last_name,
    last_modified)
  select
    null,
    1,
    'a',
    'a',
    getdate()


  goto ErrorHandler

 end

commit tran
 select 'insert successful' as result

ErrorHandler:
 begin
  Raiserror ('Insert Failed', 16, 1)
  rollback tran
 end

 
 
 

rollback tran

Post by Andrew J. Kell » Wed, 26 Sep 2001 09:07:51


Andre,

Unfortunately you can't trap an error of this severity. The only thing you
can do is to check for a successful return value of the sp (if your using

see if it's larger than what it should be and issue a rollback from outside
the sp if necessary. This is basically the result of the much too simple
error checking that TSQL provides. Your best bet is to make sure that all
the values you are inserting are valid for that table and definition before
attempting to insert them and avoid that severity of error altogether.

--
Andrew J. Kelly,   SQL Server MVP


> Andrew,

> Thanks for the reply.  I'm not sure how i would put in a rollback before
the
> raiserror.  wouldn't that mean that i'd never commit the transaction?
> you're also correct about the error being severe enough to halt the sp.
for
> instance, if you look at this snipit of my sproc below, which i've
doctored
> to die and product this error:
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'user_name', table
> 'hcp.dbo.tbl_user'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> Server: Msg 50000, Level 16, State 1, Line 29
> Insert Failed

> How can i trap for this error so it dies gracefully, instead of producing
> the above error?

> thanks, Andre

> begin tran

>  begin
>   insert  tbl_user
>    (user_name,
>     password,
>     first_name,
>     last_name,
>     last_modified)
>   select
>     null,
>     1,
>     'a',
>     'a',
>     getdate()


>   goto ErrorHandler

>  end

> commit tran
>  select 'insert successful' as result

> ErrorHandler:
>  begin
>   Raiserror ('Insert Failed', 16, 1)
>   rollback tran
>  end

 
 
 

1. ROLLBACK TRAN when long Lock occurs

I have an application (MSAccess) calling a stored
procedure via ODBC and DAO.  The sp is wrapped in a
transaction, however I need to account for locks or
deadlocks that take > 10 seconds to release.  What I want
to have happen is for my transaction to rollback and then
return an error message to the application from my sp
error handler.

I see the SET DEADLOCK_PRIORITY and SET LOCK_TIMEOUT
documentation in BOL however it is unclear on how to
implement.

Is this placed in my sp?  Used in app?  I have error
trapping in my sp that handles execution failure and via a
GOTO controls the flow to my exit_on_error marker which
rollsback the tran.  How do I control flow to my rollback
statment if a lock takes longer than 10 seconds to resolve
itself?

Thanks in advance.

Chris

2. DBCC commands

3. ROLLBACK TRAN when Deadlock occurs

4. Precedence order of locking mechanism

5. ROLLBACK TRAN / TRIGGER

6. Query Help

7. Error Handling/Rollback Tran inside Cursor - I'm stuck

8. Is there a way to tell when a DB backup is running?

9. Rollback Tran

10. Select do automatic rollback tran

11. ROLLBACK TRAN - - - RAISERROR

12. Triggers and ROLLBACK TRAN - Workaround?

13. Need to rollback tran