none
Synchronisation Script by Red gate sql compare RRS feed

  • Question

  • Hi Guys,

    Ive been trying to fathom a script generated by Red-Gate sql compare. I have basically gone through and commented a small script I wondered if someone could have a look at my comments and tell me if I am understanding what is going on properly? Here is the script:

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION  --(a)
    GO
    PRINT N'Creating [dbo].[TEST]'
    GO
    CREATE TABLE [dbo].[TEST]
    (
    [Id] [int] NULL
    )
    GO
    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION -- if there were any errors and we are in a transaction
    GO                                                     -- then rolls back to point (a)
    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END --if we arent in a transaction
    GO                                                        --(because we rolled back to point (a) in step above
                                                            --then incerement the errors table value (and begin a new
    PRINT N'Creating [dbo].[TEST2]'                            --transaction for the next update
    GO
    CREATE TABLE [dbo].[TEST2]
    (
    [Id] [int] NULL
    )
    GO
    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION --rollback to point (a) if anything has been rolled back
    GO
    IF @@TRANCOUNT>0 BEGIN --if we are in a transaction then commit it (we can only be in a tranaction at this point
    PRINT 'The database update succeeded'            --if all the updates have been successful because otherwise we
    COMMIT TRANSACTION                                --would have rolled them back immediately after each table update
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO

    If im understanding this script correctly it would seem that a transaction is set up for every update and if it gets an error then it rolls each one back and increments a counter, and if this counter is more than 0 at the end it rolls everything back. Is this script not a tad inefficient because if the first update failed then surely they should just all fail and not even bother trying them?

    Well I hope im on the right tracks with this. Any help much appreciated....

    Cheers,
    Pete
    Thursday, December 3, 2009 6:29 PM

Answers

  • Just a point of clarification - you can't nest transactions.  Well, you *can* but they don't do anything.  In other words anywhere in the code where there is a ROLLBACK, the query engine will rollback the entire transaction, all of the way up to the top.

    Your comments make sense, but I just wanted to point that statement out. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by peter_quiet Friday, December 4, 2009 2:17 PM
    Thursday, December 3, 2009 6:33 PM
    Moderator

All replies

  • Just a point of clarification - you can't nest transactions.  Well, you *can* but they don't do anything.  In other words anywhere in the code where there is a ROLLBACK, the query engine will rollback the entire transaction, all of the way up to the top.

    Your comments make sense, but I just wanted to point that statement out. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by peter_quiet Friday, December 4, 2009 2:17 PM
    Thursday, December 3, 2009 6:33 PM
    Moderator
  • Thanks for the reply Phil, thats good you pointed that out, however the microsoft documentation makes a point of explaining the @@TRANCOUNT variable to see how deeply nested you are in a transaction....

    http://msdn.microsoft.com/en-us/library/ms187967.aspx

    So it would seem that although their documentation is only explaining a point that it may be a tad misleadning?...

    With regards to the supplied script I guess its not that efficeint.

    Cheers
    Pete
    Friday, December 4, 2009 9:33 AM