How does an update script work with regards to transactions?

peter_heard01

Hi Guys,

Ive commented a sql compare update script I wondered if someone would be able to clarify if im understanding it correct, because as far as I can see the script is a bit ineffient. It seems that if any of the updates fail it rolls back but then it tries the following updates and then gets the the end and rolls them all back anyway (if one update failed). Why doesnt it just rollback the first error then skip to the end of the script?

Here is the script with comments anyway, any help much appreciated:

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

Cheers,
Pete

Brian Donahue

Hi Pete,

The transaction behavior that you mention is correct in that if there is a failure, subsequent transactions are processed and then rolled back at the end. I'm afraid I don't know if there is a good reason for this, but if the migration script is run inside the SQL Compare program, the script execution stops when a transaction fails to process.

Michelle T

One problem that running the whole thing and then rolling it all back does solve is working out what is going on when it fails - it can be quite frustrating to get the first error, solve that, and then get another one, whereas if you run the script in an external program and it fails then all the errors that will happen will be reported.