User profile for jesperkt

Hi SmithCOLE, I think you have replied to the wrong post. Your reply has no relevance for this subject. / comments

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

0 votes

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 / comments

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 votes

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. / comments

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 ...

0 votes

SQL Compare bad transaction management

Hi, I am having a problem making SQL Compare generate usable script (with a functional transaction roll back mechanism) when using Migration Scripts. We often use SQL Compare to generate DB script ...

3 followers 5 comments 0 votes

How can we help you today?

jesperkt

Activity overview