SQL Compare scripts INCORRECT error handling??

brassj

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.

peter.peart

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

brassj

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.

peter.peart

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