Posted by Matik on 09/07/06 15:17

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

[Back to original message]