|
|
Posted by Erland Sommarskog on 09/07/06 21:14
Matik (marzec@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|