What are the challenges you face when working across database platforms? Take the survey
Options

alter table missing @@error check after it results in changes with no transaction

SQL Compare has generated the following:
PRINT N'Adding foreign keys to [dbo].[Entity_PhoneNumber]'
GO
ALTER TABLE [dbo].[Entity_PhoneNumber] ADD CONSTRAINT [FK_Entity_PhoneNumber_Entity] FOREIGN KEY ([EntityID]) REFERENCES [dbo].[Entity] ([ID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Entity_PhoneNumber] ADD CONSTRAINT [FK_Entity_PhoneNumber_PhoneNumber] FOREIGN KEY ([PhoneNumberID]) REFERENCES [dbo].[PhoneNumber] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

@ERROR... after the first ALTER TABLE...

if this statement fails (due to bad data that breaks the constraint, or if it already exists) then the 2nd alter table is run without NO EXEC, and if it is successful then we don't find out that an error occurred. 

Its worse than that, because of the use of XACT ABORT, the first line causes an error, and the transaction is rolled back, but all subsequent lines are done OUTSIDE of a transaction!!

This is very bad indeed!
Sign In or Register to comment.