DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: how to rollback a stored proc transaction

  1. #1
    ranga raghunathan Guest

    how to rollback a stored proc transaction


    hello

    got a mts class(requires transaction) method which executes a stored proc
    .. in the middle of the stored proc, i am doing a calculation involving a
    variable say x and if x=1 i want the
    stored proc to stop execution and the transaction to rollback .. note that
    per MTS model i DONT have any begintrans or rollback trans code in the stored
    proc. i tried using
    raiseerror but it is not rolling the transction back.

    thanks
    ranga

  2. #2
    Rahul Kapoor Guest

    Re: how to rollback a stored proc transaction

    A SetAbrot should do it. (In case you are using W2k use the new refactored
    interfaces i.e. MyTransavtionVote method ! lookup the MSDN for details)

    If you are using COM+ then you can also set AutoAbort declaratively.

    Regards
    Rahul

    "ranga raghunathan" <ranga1@msn.com> wrote in message
    news:39367435$1@news.devx.com...
    >
    > hello
    >
    > got a mts class(requires transaction) method which executes a stored proc
    > . in the middle of the stored proc, i am doing a calculation involving a
    > variable say x and if x=1 i want the
    > stored proc to stop execution and the transaction to rollback .. note

    that
    > per MTS model i DONT have any begintrans or rollback trans code in the

    stored
    > proc. i tried using
    > raiseerror but it is not rolling the transction back.
    >
    > thanks
    > ranga




  3. #3
    ranga raghunathan Guest

    Re: how to rollback a stored proc transaction


    Rahul

    thanks for your reply, but you did not understand the question.
    I cannot call setabort within a stored proc. I want to exit out of the stored
    proc if a certain condition exists in the proecedure - i tried using raiseerror
    in the stored proc, but it does not seem to pass the error to the class method..


    one alternative i have is to use the RETURN statement in the stored proc
    and return a value say 3 when a certain condition exists . then in the class
    method, i will check this return value and if it is 3 then i will call setabort

    "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    >A SetAbrot should do it. (In case you are using W2k use the new refactored
    >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for details)
    >
    >If you are using COM+ then you can also set AutoAbort declaratively.
    >
    >Regards
    >Rahul
    >
    >"ranga raghunathan" <ranga1@msn.com> wrote in message
    >news:39367435$1@news.devx.com...
    >>
    >> hello
    >>
    >> got a mts class(requires transaction) method which executes a stored proc
    >> . in the middle of the stored proc, i am doing a calculation involving

    a
    >> variable say x and if x=1 i want the
    >> stored proc to stop execution and the transaction to rollback .. note

    >that
    >> per MTS model i DONT have any begintrans or rollback trans code in the

    >stored
    >> proc. i tried using
    >> raiseerror but it is not rolling the transction back.
    >>
    >> thanks
    >> ranga

    >
    >



  4. #4
    Rahul Kapoor Guest

    Re: how to rollback a stored proc transaction

    What params are you using for RaiseError (severity et al !)

    Rahul

    "ranga raghunathan" <ranga1@msn.com> wrote in message
    news:3936ff56$1@news.devx.com...
    >
    > Rahul
    >
    > thanks for your reply, but you did not understand the question.
    > I cannot call setabort within a stored proc. I want to exit out of the

    stored
    > proc if a certain condition exists in the proecedure - i tried using

    raiseerror
    > in the stored proc, but it does not seem to pass the error to the class

    method..
    >
    >
    > one alternative i have is to use the RETURN statement in the stored proc
    > and return a value say 3 when a certain condition exists . then in the

    class
    > method, i will check this return value and if it is 3 then i will call

    setabort
    >
    > "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    > >A SetAbrot should do it. (In case you are using W2k use the new

    refactored
    > >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for details)
    > >
    > >If you are using COM+ then you can also set AutoAbort declaratively.
    > >
    > >Regards
    > >Rahul
    > >
    > >"ranga raghunathan" <ranga1@msn.com> wrote in message
    > >news:39367435$1@news.devx.com...
    > >>
    > >> hello
    > >>
    > >> got a mts class(requires transaction) method which executes a stored

    proc
    > >> . in the middle of the stored proc, i am doing a calculation involving

    > a
    > >> variable say x and if x=1 i want the
    > >> stored proc to stop execution and the transaction to rollback .. note

    > >that
    > >> per MTS model i DONT have any begintrans or rollback trans code in the

    > >stored
    > >> proc. i tried using
    > >> raiseerror but it is not rolling the transction back.
    > >>
    > >> thanks
    > >> ranga

    > >
    > >

    >




  5. #5
    ranga raghunathan Guest

    Re: how to rollback a stored proc transaction


    my test stored proc is simple 2 lines

    insert into tblMTSTest values("22")
    RAISERROR( 'raise',12,1)

    the 12 and 1 are arbitrary, for testing only
    i there any specific values i need to use for these arguments
    for the stored proc to pass the error back to the client ?


    "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    >What params are you using for RaiseError (severity et al !)
    >
    >Rahul
    >
    >"ranga raghunathan" <ranga1@msn.com> wrote in message
    >news:3936ff56$1@news.devx.com...
    >>
    >> Rahul
    >>
    >> thanks for your reply, but you did not understand the question.
    >> I cannot call setabort within a stored proc. I want to exit out of the

    >stored
    >> proc if a certain condition exists in the proecedure - i tried using

    >raiseerror
    >> in the stored proc, but it does not seem to pass the error to the class

    >method..
    >>
    >>
    >> one alternative i have is to use the RETURN statement in the stored proc
    >> and return a value say 3 when a certain condition exists . then in the

    >class
    >> method, i will check this return value and if it is 3 then i will call

    >setabort
    >>
    >> "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    >> >A SetAbrot should do it. (In case you are using W2k use the new

    >refactored
    >> >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for details)
    >> >
    >> >If you are using COM+ then you can also set AutoAbort declaratively.
    >> >
    >> >Regards
    >> >Rahul
    >> >
    >> >"ranga raghunathan" <ranga1@msn.com> wrote in message
    >> >news:39367435$1@news.devx.com...
    >> >>
    >> >> hello
    >> >>
    >> >> got a mts class(requires transaction) method which executes a stored

    >proc
    >> >> . in the middle of the stored proc, i am doing a calculation involving

    >> a
    >> >> variable say x and if x=1 i want the
    >> >> stored proc to stop execution and the transaction to rollback .. note
    >> >that
    >> >> per MTS model i DONT have any begintrans or rollback trans code in

    the
    >> >stored
    >> >> proc. i tried using
    >> >> raiseerror but it is not rolling the transction back.
    >> >>
    >> >> thanks
    >> >> ranga
    >> >
    >> >

    >>

    >
    >



  6. #6
    Rahul Kapoor Guest

    Re: how to rollback a stored proc transaction

    use RaisError('Msg', 16, 1)

    i.e. use 16 for the severity level !

    Hope that helps.

    Rahul

    "ranga raghunathan" <ranga1@msn.com> wrote in message
    news:3937fc04@news.devx.com...
    >
    > my test stored proc is simple 2 lines
    >
    > insert into tblMTSTest values("22")
    > RAISERROR( 'raise',12,1)
    >
    > the 12 and 1 are arbitrary, for testing only
    > i there any specific values i need to use for these arguments
    > for the stored proc to pass the error back to the client ?
    >
    >
    > "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    > >What params are you using for RaiseError (severity et al !)
    > >
    > >Rahul
    > >
    > >"ranga raghunathan" <ranga1@msn.com> wrote in message
    > >news:3936ff56$1@news.devx.com...
    > >>
    > >> Rahul
    > >>
    > >> thanks for your reply, but you did not understand the question.
    > >> I cannot call setabort within a stored proc. I want to exit out of the

    > >stored
    > >> proc if a certain condition exists in the proecedure - i tried using

    > >raiseerror
    > >> in the stored proc, but it does not seem to pass the error to the class

    > >method..
    > >>
    > >>
    > >> one alternative i have is to use the RETURN statement in the stored

    proc
    > >> and return a value say 3 when a certain condition exists . then in the

    > >class
    > >> method, i will check this return value and if it is 3 then i will call

    > >setabort
    > >>
    > >> "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    > >> >A SetAbrot should do it. (In case you are using W2k use the new

    > >refactored
    > >> >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for

    details)
    > >> >
    > >> >If you are using COM+ then you can also set AutoAbort declaratively.
    > >> >
    > >> >Regards
    > >> >Rahul
    > >> >
    > >> >"ranga raghunathan" <ranga1@msn.com> wrote in message
    > >> >news:39367435$1@news.devx.com...
    > >> >>
    > >> >> hello
    > >> >>
    > >> >> got a mts class(requires transaction) method which executes a stored

    > >proc
    > >> >> . in the middle of the stored proc, i am doing a calculation

    involving
    > >> a
    > >> >> variable say x and if x=1 i want the
    > >> >> stored proc to stop execution and the transaction to rollback ..

    note
    > >> >that
    > >> >> per MTS model i DONT have any begintrans or rollback trans code in

    > the
    > >> >stored
    > >> >> proc. i tried using
    > >> >> raiseerror but it is not rolling the transction back.
    > >> >>
    > >> >> thanks
    > >> >> ranga
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  7. #7
    Bill Slater Guest

    Re: how to rollback a stored proc transaction

    <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
    <html>
    I don't think that is the problem since any error level greater than 10
    should generate a trabble error to the client. Try the following:
    <p>Put a :
    <br>SET NOCOUNT ON
    <br>statement as the first line of your SP. See&nbsp; <a href="http://www.able-consulting.com/ADO_Faq.htm#Q38">ADO
    FAQ</a>&nbsp; Q38 for more info.
    <p>Also, are you running the COM object in debug mode or is it compiled
    and installed in MTS? When you run in debug mode, the object acts as if
    it enlists the transaction but it actually does not. Typically the only
    way to debug transaction problems is to add event log messages to trace
    code while the object is actually running in MTS.
    <p>Finally, be *sure* that your code will actually call SetAbort when an
    error is raised. You might try posting the full client method and SP source.
    <p>Rahul Kapoor wrote:
    <blockquote TYPE=CITE>use RaisError('Msg', 16, 1)
    <p>i.e. use 16 for the severity level !
    <p>Hope that helps.
    <p>Rahul
    <p>"ranga raghunathan" &lt;ranga1@msn.com> wrote in message
    <br><a href="news:3937fc04@news.devx.com">news:3937fc04@news.devx.com</a>...
    <br>>
    <br>> my test stored proc is simple 2 lines
    <br>>
    <br>> insert into tblMTSTest values("22")
    <br>> RAISERROR( 'raise',12,1)
    <br>>
    <br>> the 12 and 1 are arbitrary, for testing only
    <br>> i there any specific values i need to use for these arguments
    <br>> for the stored proc to pass the error back to the client ?
    <br>>
    <br>>
    <br>> "Rahul Kapoor" &lt;r_ahu_l@yahoo.com> wrote:
    <br>> >What params are you using for RaiseError (severity et al !)
    <br>> >
    <br>> >Rahul
    <br>> >
    <br>> >"ranga raghunathan" &lt;ranga1@msn.com> wrote in message
    <br>> ><a href="news:3936ff56$1@news.devx.com">news:3936ff56$1@news.devx.com</a>...
    <br>> >>
    <br>> >> Rahul
    <br>> >>
    <br>> >> thanks for your reply, but you did not understand the question.
    <br>> >> I cannot call setabort within a stored proc. I want to exit out
    of the
    <br>> >stored
    <br>> >> proc if a certain condition exists in the proecedure&nbsp; - i
    tried using
    <br>> >raiseerror
    <br>> >> in the stored proc, but it does not seem to pass the error to
    the class
    <br>> >method..
    <br>> >>
    <br>> >>
    <br>> >> one alternative i have is to use the RETURN statement in the stored
    <br>proc
    <br>> >> and return a value say 3 when a certain condition exists . then
    in the
    <br>> >class
    <br>> >> method, i will check this return value and if it is 3 then i will
    call
    <br>> >setabort
    <br>> >>
    <br>> >> "Rahul Kapoor" &lt;r_ahu_l@yahoo.com> wrote:
    <br>> >> >A SetAbrot should do it. (In case you are using W2k use the new
    <br>> >refactored
    <br>> >> >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for
    <br>details)
    <br>> >> >
    <br>> >> >If you are using COM+ then you can also set AutoAbort declaratively.
    <br>> >> >
    <br>> >> >Regards
    <br>> >> >Rahul
    <br>> >> >
    <br>> >> >"ranga raghunathan" &lt;ranga1@msn.com> wrote in message
    <br>> >> ><a href="news:39367435$1@news.devx.com">news:39367435$1@news.devx.com</a>...
    <br>> >> >>
    <br>> >> >> hello
    <br>> >> >>
    <br>> >> >> got a mts class(requires transaction) method which executes
    a stored
    <br>> >proc
    <br>> >> >> . in the middle of the stored proc, i am doing a calculation
    <br>involving
    <br>> >> a
    <br>> >> >> variable say x and if x=1 i want the
    <br>> >> >> stored proc to stop execution and the transaction to rollback
    ...
    <br>note
    <br>> >> >that
    <br>> >> >> per MTS model i DONT have any begintrans or rollback trans
    code in
    <br>> the
    <br>> >> >stored
    <br>> >> >> proc. i tried using
    <br>> >> >> raiseerror but it is not rolling the transction back.
    <br>> >> >>
    <br>> >> >> thanks
    <br>> >> >> ranga
    <br>> >> >
    <br>> >> >
    <br>> >>
    <br>> >
    <br>> >
    <br>></blockquote>
    </html>


  8. #8
    ranga raghunathan Guest

    Re: how to rollback a stored proc transaction


    thank you bill
    yes, you are correct. the setnocount on made the trick

    thanks
    ranga
    Bill Slater <bslater@home.com> wrote:
    ><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
    ><html>
    >I don't think that is the problem since any error level greater than 10
    >should generate a trabble error to the client. Try the following:
    ><p>Put a :
    ><br>SET NOCOUNT ON
    ><br>statement as the first line of your SP. See <a href="http://www.able-consulting.com/ADO_Faq.htm#Q38">ADO
    >FAQ</a> Q38 for more info.
    ><p>Also, are you running the COM object in debug mode or is it compiled
    >and installed in MTS? When you run in debug mode, the object acts as if
    >it enlists the transaction but it actually does not. Typically the only
    >way to debug transaction problems is to add event log messages to trace
    >code while the object is actually running in MTS.
    ><p>Finally, be *sure* that your code will actually call SetAbort when an
    >error is raised. You might try posting the full client method and SP source.
    ><p>Rahul Kapoor wrote:
    ><blockquote TYPE=CITE>use RaisError('Msg', 16, 1)
    ><p>i.e. use 16 for the severity level !
    ><p>Hope that helps.
    ><p>Rahul
    ><p>"ranga raghunathan" <ranga1@msn.com> wrote in message
    ><br><a href="news:3937fc04@news.devx.com">news:3937fc04@news.devx.com</a>...
    ><br>>
    ><br>> my test stored proc is simple 2 lines
    ><br>>
    ><br>> insert into tblMTSTest values("22")
    ><br>> RAISERROR( 'raise',12,1)
    ><br>>
    ><br>> the 12 and 1 are arbitrary, for testing only
    ><br>> i there any specific values i need to use for these arguments
    ><br>> for the stored proc to pass the error back to the client ?
    ><br>>
    ><br>>
    ><br>> "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    ><br>> >What params are you using for RaiseError (severity et al !)
    ><br>> >
    ><br>> >Rahul
    ><br>> >
    ><br>> >"ranga raghunathan" <ranga1@msn.com> wrote in message
    ><br>> ><a href="news:3936ff56$1@news.devx.com">news:3936ff56$1@news.devx.com</a>...
    ><br>> >>
    ><br>> >> Rahul
    ><br>> >>
    ><br>> >> thanks for your reply, but you did not understand the question.
    ><br>> >> I cannot call setabort within a stored proc. I want to exit out
    >of the
    ><br>> >stored
    ><br>> >> proc if a certain condition exists in the proecedure - i
    >tried using
    ><br>> >raiseerror
    ><br>> >> in the stored proc, but it does not seem to pass the error to
    >the class
    ><br>> >method..
    ><br>> >>
    ><br>> >>
    ><br>> >> one alternative i have is to use the RETURN statement in the stored
    ><br>proc
    ><br>> >> and return a value say 3 when a certain condition exists . then
    >in the
    ><br>> >class
    ><br>> >> method, i will check this return value and if it is 3 then i will
    >call
    ><br>> >setabort
    ><br>> >>
    ><br>> >> "Rahul Kapoor" <r_ahu_l@yahoo.com> wrote:
    ><br>> >> >A SetAbrot should do it. (In case you are using W2k use the new
    ><br>> >refactored
    ><br>> >> >interfaces i.e. MyTransavtionVote method ! lookup the MSDN for
    ><br>details)
    ><br>> >> >
    ><br>> >> >If you are using COM+ then you can also set AutoAbort declaratively.
    ><br>> >> >
    ><br>> >> >Regards
    ><br>> >> >Rahul
    ><br>> >> >
    ><br>> >> >"ranga raghunathan" <ranga1@msn.com> wrote in message
    ><br>> >> ><a href="news:39367435$1@news.devx.com">news:39367435$1@news.devx.com</a>...
    ><br>> >> >>
    ><br>> >> >> hello
    ><br>> >> >>
    ><br>> >> >> got a mts class(requires transaction) method which executes
    >a stored
    ><br>> >proc
    ><br>> >> >> . in the middle of the stored proc, i am doing a calculation
    ><br>involving
    ><br>> >> a
    ><br>> >> >> variable say x and if x=1 i want the
    ><br>> >> >> stored proc to stop execution and the transaction to rollback
    >...
    ><br>note
    ><br>> >> >that
    ><br>> >> >> per MTS model i DONT have any begintrans or rollback trans
    >code in
    ><br>> the
    ><br>> >> >stored
    ><br>> >> >> proc. i tried using
    ><br>> >> >> raiseerror but it is not rolling the transction back.
    ><br>> >> >>
    ><br>> >> >> thanks
    ><br>> >> >> ranga
    ><br>> >> >
    ><br>> >> >
    ><br>> >>
    ><br>> >
    ><br>> >
    ><br>></blockquote>
    ></html>
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center