Options

Use of transactions in the generated script

Randy MinderRandy Minder Posts: 4
edited April 23, 2005 3:03PM in SQL Compare Previous Versions
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
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
This discussion has been closed.