Nested SQL Transactions in SQL Server

Question

I have two stored procedures, I call one stored procedure from another stored procedure and in both two stored procedures I use transactions. Below is the stored procedures,

CREATE PROCEDURE [dbo].[spOuter]    
AS
BEGIN
    begin tran t1
    -- some sql queries..
    Exec spInner;

    commit tran t1
END


CREATE PROCEDURE [dbo].[spInner] 
AS
BEGIN

    begin tran t2
    – some sql queries.
    rollback tran t2

END

but when executing this, It shows error as shown below:

Cannot roll back t2. No transaction or savepoint of that name was found.

Can anybody explain why is it showing?

Answer 1

Read this articles, you will get some better ideas...

http://www.codemag.com/article/0305111

http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx