Red Gate forums :: View topic - SQL Compare bad transaction management
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare bad transaction management

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jesperkt



Joined: 08 Jan 2014
Posts: 4
Location: Copenhagen, Denmark

PostPosted: Wed Jan 08, 2014 10:38 am    Post subject: SQL Compare bad transaction management Reply with quote

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 to be run on our customer's databases. If the intermediary SVN checkin(s) contain only auto generated code (i.e. no migration script), SQL Compare adds the following code block after every GO

Code:

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


This ensures that the script is fully rollbacked if an error occurs at any time during the execution.


Unfortunately, if the intermediary SVN checkin has a Migration Script, these blocks of code are not added. Though the resulting script still has a BEGIN/ROLLBACK/COMMIT TRANSACTION block these do not really do anything useful. Any error in the middle of the script will result in partial commits.


I hope someone can help me with this problem. Hopefully I am merely missing a setting somewhere.
_________________
Jesper Thygesen
Back to top
View user's profile Send private message
jesperkt



Joined: 08 Jan 2014
Posts: 4
Location: Copenhagen, Denmark

PostPosted: Wed Jan 08, 2014 10:59 am    Post subject: Reply with quote

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

Code:

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.

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

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.
_________________
Jesper Thygesen
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Fri Jan 10, 2014 2:55 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jesperkt



Joined: 08 Jan 2014
Posts: 4
Location: Copenhagen, Denmark

PostPosted: Mon Jan 20, 2014 1:05 pm    Post subject: Reply with quote

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?

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

_________________
Jesper Thygesen
Back to top
View user's profile Send private message
SmithCOLE



Joined: 27 Jan 2014
Posts: 1

PostPosted: Tue Jan 28, 2014 9:58 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jesperkt



Joined: 08 Jan 2014
Posts: 4
Location: Copenhagen, Denmark

PostPosted: Tue Jan 28, 2014 10:03 am    Post subject: Reply with quote

Hi SmithCOLE,

I think you have replied to the wrong post. Your reply has no relevance for this subject.
_________________
Jesper Thygesen
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group