Pblm with Nested transactions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pblm with Nested transactions

Author

Topic

sqlpgmr
Starting Member

4 Posts

Posted - 2005-11-30 : 01:14:32

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-30 : 07:48:57

That kind of error sounds like there is some other problem. Can you post some working DDL/DML that actually generates the error? It can be a simplified version like you have but make it real, executable code against actual tables.

Is there any condition where you want one of your transactions to commit and the other to rollback? If not, get rid of the inner transaction. Set a return code or something in your inner sproc that will indicate the "success" status so that the outer knows if it should commit or rollback.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources