SQL Compare bad transaction management

• 
  In case my explanation above does not make sense, here is a clarification based on a simplified case of a single SVN checkin.
  
  If I attempt to add a Migration Script to this check in, the following code is generated for me (note that I have removed a lot of lines to make my case more clear to the reader).
  

  EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Timesheets', 'COLUMN', N'Rostered'
  GO
  ALTER TABLE [dbo].[Timesheets] DROP
  COLUMN [Rostered]
  GO
  

  
  Now I do not change this code, but I still save it as a migration script. Then I use SQL Compare to generate a script for me that I can use to add this change to an foreign database. The resulting code will looks something like this.
  

  CREATE TABLE #tmpErrors (Error int)
  GO
  BEGIN TRANSACTION
  GO
  EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Timesheets', 'COLUMN', N'Rostered'
  GO
  ALTER TABLE [dbo].[Timesheets] DROP
  COLUMN [Rostered]
  GO
  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
  

  
  Since we never put anything inside the #tmpErrors table, this transaction will never rollback, and we could end up with a partial check in (maybe not in this simplified case, but in more complex ones).
  
  
  If I delete my migration script and try SQL Compare again, it generates much better code:
  

  CREATE TABLE #tmpErrors (Error int)
  GO
  BEGIN TRANSACTION
  GO
  EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Timesheets', 'COLUMN', N'Rostered'
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  ALTER TABLE [dbo].[Timesheets] DROP
  COLUMN [Rostered]
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  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
  

  
  This code IS transaction safe. Either it will all be committed or none of it will.

  jesperkt January 08, 2014 04:59

  - 0 +
• 
  Thanks for letting us know. I have logged a bug (SC-6765) so this can be looked into. SQL Compare is not including your own migration script code into the transaction handling framework of SQL Compare.

  Brian Donahue January 10, 2014 08:55

  - 0 +
• 
  Anything new on this subject?
  
  Since a bug has been logged, does this mean that Migration Script SHOULD have been padded with the be padded with the code below after every command?
  

  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION 
  GO 
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END 
  GO
  

  jesperkt January 20, 2014 07:05

  - 0 +
• 
  Hi!!
  Thanks a lot for providing information about SQL Compare.SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.SQL databases use long-established standard which is being
  adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.

  SmithCOLE January 28, 2014 03:58

  - 0 +
• 
  Hi SmithCOLE,
  
  I think you have replied to the wrong post. Your reply has no relevance for this subject.

  jesperkt January 28, 2014 04:03

  - 0 +

Add comment

Please sign in to leave a comment.