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
Hi Pete,
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 GOCheers,
Pete