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
--SQLPgmrbegin 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!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment