Use of transactions in the generated script

Randy Minder

It's my understanding that when the script SQL Compare produces is executed, if any error is generated, all changes up to that point are rolled back and execution of the script stops. Is this correct?

The reason I ask is because of the following script fragment shown below. It looks to me like if the Alter Table step fails, the transaction is rolled back but another one is immediately started and the script continues.

Thanks for you clarification on this.

Randy Minder


ALTER TABLE [dbo].[ProjectPolicies] ADD
[OrganizationOverrideRowID] [int] NULL
GO

@TRANCOUNT>0 ROLLBACK TRANSACTION
GO

@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

Brian Donahue

Hi Randy,

That seems to be the case. Each object modification is rolled up in its' own nested transaction:

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SystemSW]'
GO
CREATE TABLE [dbo].[SystemSW]
(
[System] [int] NOT NULL,
[SW] [int] NOT NULL
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

If the creation of table 'SystemSW' fails, the individual transaction is rolled back, but the 'larger' transaction is still being processed. The script should keep running all of the rest of the way through, then, at the end, the #tmperrors table is checked and the whole thing is rolled back if [Error]=1.