Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    8

    Question Unanswered: Pblm with Nested transactions

    Friends,

    I've a very basic doubt with Nested transactions (across procedures) in SQL Server and i guess the given below sample code illustarates my doubt well more than my words ..

    I've a Proc1 like this

    create procedure sp_proc1
    as
    begin
    begin tran sp_proc1
    insert into tab1 values (1,2)
    exec sp_proc2 1
    if <Some cdn statement>
    rollback tran sp_proc1
    else
    commit tran sp_proc1
    end

    and called proc sp_proc2 is like this

    create procedure sp_proc2
    (
    @val1 int
    )
    as
    begin tran proc2
    update tab2 set col1 = 5
    IF <some cdn statement>
    begin
    rollback tran proc2
    end
    else
    begin
    commit tran proc2
    end

    The pblm is when the 1st proc is executed and when the cdn statement in the 2nd proc is sucess, then it results with the error

    Failed to retreive execution plan: Cannot roll back proc2. No transaction or savepoint of that name was found.

    any suggestions

    --SQLPgmr
    Last edited by sqlpgmr; 11-30-05 at 02:37.

  2. #2
    Join Date
    Sep 2002
    Posts
    24
    begin tran proc2
    update tab2 set col1 = 5
    IF <some cdn statement> << Checking your tab2 but it still in tran !
    begin
    ...


    I think you probably are trying to check some thing that you are not commit yet!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •