CODEWITHSUNDEEP
sqlsql-servertransactionsrollback
user3166407
user3166407

Reputation: 65

Nested SQL Transactions in SQL Server

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?

Upvotes: 3

Views: 413

Answers (1)

Vinu
Vinu

Reputation: 1292

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

Upvotes: 1

Related Questions