SQL Compare scripts INCORRECT error handling??

brassjbrassj Posts: 6
edited June 4, 2010 7:35AM in SQL Compare Previous Versions
PRINT N'Altering [dbo].[linerLogMessageType]'
GO
ALTER TABLE [dbo].[linerLogMessageType] ALTER COLUMN [name] [nvarchar] (160) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[linerLogMessageType] ALTER COLUMN [dateTimeStamp] [datetime] NOT NULL
ALTER TABLE [dbo].[linerLogMessageType] ALTER COLUMN [hostname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT 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


@ERROR' only catch errors from the last 'UPDATE' and the first two could generate uncaught errors?

Environment:
SQL Compare 8.1.0.360 Unlicensed trial
SQL Server 2005

PS: I just found a June 26th, 2008 about lunch easter egg.

Comments

  • Hi there,

    The entire script that SQL Compare generates (unless you specify otherwise in the options) is wrapped in a single transaction. With this in mind, if you have a failure then the entier script will be rolled back.

    In this particular scenario, neither of the first 2 ALTER TABLE statements have been committed, and wouldn't get committed until the end of the synch script. The error handling in place here is almost a marker if you will that puts the errors in a temp table, which is then double checked at the end of the script being generated. If there are errors, the entier script is rolled back. Sample script for the rollback is detailed below:
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO
    

    HTH!

    Pete
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • Hi, Peter
    Sorry for the long delay in reply as my browser was pulling from a cache or something and I didn't realize there was a reply for me.

    @ERROR would be equal to 0 and you wouldn't catch that there was an error. Since the error isn't caught, there would be no 'ROLLBACK' and no rows inserted into #tmpErrors. (Or at least that is how I read the script.)


    @ERROR check but because of the
    SET XACT_ABORT ON
    GO
    
    that is at the beginning of the script. I've never used SET XACT_ABORT ON; so I'm unsure of how that effects everything. I was testing it last night and I'll test it some more tonight.
  • Hi there,

    Yes, this also rolls back the entire script. How did you get on with your testing, did you require any additional assistance at all?

    Pete
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
Sign In or Register to comment.