transaction inside sp_executesql

[Matik]

Hi to all,

Probably I'm just doing something stupid, but I would like you to tell
me that (if it is so), and point the solution.

There ist the thing:

I' having a sp, where I call other sp inside.
The only problem is, the name of this inside sp is builded variously,
and executed over sp_executesql:

create pprocedure major_sp
@prm_outer_1 varchar(1),
@prm_outer_2 varchar(2)
as

some coding

set @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +
@prm_outer_2
set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4
output'

set @nvar_prmStr = N'@prm_1 nvarchar(128), ' +
N'@prm_2 nvarchar(128), ' +
N'@prm_3 nvarchar(4000), ' +
N'@int_exRetCode int output, ' +
N'@prm_4 varchar(64) output'

exec sp_executesql @nvar_stmtStr,
@nvar_prmStr,
@prm_1,
@prm_2,
@prm_3,
@int_exRetCode = @int_exRetCode output,
@prm_4 = @prm_4 output
Now the issue is, I've transactions inside test_sp_11 lets say where
the 11 is @prm_outer_1 + @prm_outer_2.
These procedures are existing inside database, but are called dynamicly
depending of the parameters.

The problem is, when I call the specified sp directly, the rollback
transaction is working without any problem.
Inside this procedures test_sp_xx, is a call of another sp (lets say
inside_sp).
There is a transaction included.
When it is called over major_sp, then the rollback is not performed
because of error:

Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54
Cannot roll back transactio_bubu. No transaction or savepoint of that
name was found.

The funniest way is, if there is no error inside, the commit is working
without any problem!

The question is majory (because I'm almost sure, that this is an
issue): is it possible, to have a
transaction inside dynamicly called sp over sp_executesql?
If ok to do that?

Thank's in advance

Matik

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

I' having a sp, where I call other sp inside.
The only problem is, the name of this inside sp is builded variously,
and executed over sp_executesql:

There is no need for that. Simply do:

SELECT @spname = test_sp_' + @prm_outer_1 + @prm_outer_2
EXEC @ret = @spname @prm_1, @prm_2, @prm_3, @prm_4 OUTPUT

The problem is, when I call the specified sp directly, the rollback
transaction is working without any problem.
Inside this procedures test_sp_xx, is a call of another sp (lets say
inside_sp).
There is a transaction included.
When it is called over major_sp, then the rollback is not performed
because of error:

Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54
Cannot roll back transactio_bubu. No transaction or savepoint of that
name was found.

The funniest way is, if there is no error inside, the commit is working
without any problem!

This nothing to do with dynamic SQL at all. It has to do with named
transaction, a feature that I have will have to admit never really
found the point with.

Here is a repro that shows what is going on:

begin transaction "outer"
begin transaction "inner"
rollback transaction "inner"
rollback transaction

This yields the same error message as you got. But if you only run the
two lines in the middle, then it works as advertised.

The key issue is here when you nest BEGIN TRANSACTION, the inner of
them only increases the transaction counter. When you commit a nested
transaction, you only decrease that counter if the counter is 1.

But a ROLLBACK is different. A ROLLBACK always rolls back it all. Here
you ask to rollback to "inner" but you can't, because there is no
transaction with that name.

If you instead change the inner BEGIN TRANSACTION to SAVE TRANSACTION,
the rollback will succeed. You have then defined a savepoint to which
a transaction can be rolled back and then continue from that point.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx