Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 124
sql 123
ms sql server 118
ms sql 117
database 108
tsql 80
#SQL Server 78
t-sql 74
#sql 71
sql server general 66

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"

Oct 2 2011 12:00AM by Paresh Prajapati   

During development of stored procedures, i ran into one issue and this was due to not applied best practice of stored procedures practice and this issue comes mostly when we call stored procedures inside another one and so on, which are used in nested in transactions. So i  would like to write next post for the stored procedures development , as how we should write it, It will be a just a template of best practice. In this post i will explain the details of the issue which i suffered and how can we resolve it.

Continue Reading...

Tags: sql server, #SQLServer, data,


Paresh Prajapati
6 · 23% · 7533
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Although I agree with the gist of the first and the last code-block, I'm pretty sure the one in the middle does NOT do what you think it does.

    Disclaimer : I'm not sure how this behaves under Oracle, but the named transactions simply don't work like that in MSSQL.

    To explain this I think adding a simple PRINT of the error-message in the CATCH of both stored procedures will show that (as expected) there is a PK violation in Firttranproc but that you end up in the CATCH block of Secondtranproc because the ROLLBACK in Firttranproc didn't go through! (**)

    -- Altering first stored procedure here
    ALTER PROCEDURE Firttranproc
    AS
      BEGIN
          SET NOCOUNT ON
    
       -- Here we have specified Tran1 as transaction name
    
       BEGIN TRY
              BEGIN TRANSACTION Tran1
    
              INSERT INTO tbl_Tran
                          (TranId
                           ,TranName)
    
              SELECT
                1
                ,'Tran-1'
              UNION ALL
              SELECT
                1
                ,'Tran-1'
    
              COMMIT TRANSACTION Tran1
          END TRY
    
          BEGIN CATCH
              PRINT 'Firttranproc error : ' + ERROR_MESSAGE()
              PRINT 'Rollback Tran1'
    
              ROLLBACK TRANSACTION Tran1
          END CATCH
      END
    
    GO
    
    -- Altering second stored procedure here
    ALTER PROCEDURE Secondtranproc
    AS
      BEGIN
          SET NOCOUNT ON
    
      -- Here we have specified Tran2 as transaction name
    
          BEGIN TRY
              BEGIN TRANSACTION Tran2
    
        -- Inserting records
              INSERT INTO tbl_Tran
                          (TranId
                           ,TranName)
              SELECT
                2
                ,'Tran-2'
    
              -- Calling first stored procedure here
              EXEC Firttranproc
    
              COMMIT TRANSACTION Tran2
          END TRY
    
          BEGIN CATCH
              PRINT 'Secondtranproc error : ' + ERROR_MESSAGE()
              PRINT 'Rollback Tran2'
    
              ROLLBACK TRANSACTION Tran2
          END CATCH
      END
    
    GO
    
    
    -- Executing second stored procedure which will also call of SP1 
    EXEC Secondtranproc
    

    will then result in :

    Firttranproc error : Violation of PRIMARY KEY constraint 'PK__tbl_Tran__F70897C905E3CDB6'. Cannot insert duplicate key in object 'dbo.tbl_Tran'.
    Rollback Tran1
    Secondtranproc error : Cannot roll back Tran1. No transaction or savepoint of that name was found.
    Rollback Tran2
    

    I'm not sure yet why the ROLLBACK in Secondtranproc doesn't show an error then, but I'm guessing here that this is because that transaction is the 'outer' TRANSACTION (where @@TRANCOUNT goes from 0 to 1) which can be rolled back 'name-wise' without creating a savepoint explicitly.

    Named transactions would be 'great stuff' if they wouldn't be so ridiculously cumbersome to set up =/

    (**: always check WHY you ended up in a CATCH block, never assume you know why. Think about it : if you just run Firttranproc, there is no error raised at all, it's eaten entirely by the TRY..CATCH, so then why did you end up in the CATCH block of Secondtranproc ??)

    commented on Oct 6 2011 4:29AM
    Roby Van Hoye
    224 · 1% · 210
  • @Roby Van Hoye: I agree with you. The middle code is absolutely not doing what it appears to be doing.

    Nested transactions DO NOT exist in SQL Server. Whenever a ROLLBACK is issued, the rollback happens all the way to the outermost open transaction - unless of course, we are rolling back to a known SAVEPOINT. I had a whole series of Question-of-the-Day on SQL Server Central demonstrating this. You can also read more at: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx

    Paresh: Books On Line (http://msdn.microsoft.com/en-us/library/ms181299.aspx) also mentions this: "*ROLLBACK TRANSACTION without a savepointname or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.*"

    commented on Oct 22 2011 11:57PM
    Nakul Vachhrajani
    4 · 36% · 11648

Your Comment


Sign Up or Login to post a comment.

"Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"" rated 5 out of 5 by 3 readers
Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]