November 29, 2005 at 11:35 pm
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
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
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
November 30, 2005 at 2:35 am
I think that there may be some misunderstanding on how you can 'nest' transactions. Thing is, that transactions cannot be nested in any other way than syntax.
I've posted a short explanation of how transactions work in SQL Server here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=218671#bm218747
BOL also has very good information about transaction handling in SQL Server.
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply