Red Gate forums :: View topic - Setting for transaction isolation level not used in script
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

Setting for transaction isolation level not used in script

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



Joined: 25 Jan 2011
Posts: 9

PostPosted: Thu Sep 05, 2013 8:00 am    Post subject: Setting for transaction isolation level not used in script Reply with quote

Hi,

When i set the transaction isolation level to READ COMMITTED under Tools, Application Options, and create a new compare project, the setting is not used in the generated scripts. Instead the transaction isolation level is set to SERIALIZABLE.

I really want this option to work, because we use a lot of replicated objects, and now (if I forget to change the script) we get errors during the deploy.

Anyone any bright ideas?

Thanks,
Jos
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Mon Sep 09, 2013 11:44 am    Post subject: Reply with quote

Can you please clarify... are you running the project through the command-line version? Because the transaction isolation level is a global option, it does not appear in the project and this trips people up all the time.

You have to specify the transaction isolation level on the command-line using the command-line, for example /til:"READ COMMITTED"

Please let us know if this works.
Back to top
View user's profile Send private message
JosvanDuijn



Joined: 25 Jan 2011
Posts: 9

PostPosted: Mon Sep 09, 2013 12:15 pm    Post subject: Reply with quote

I'm using the GUI only.
I set the option under Tools, Application options to 'READ COMMITTED' and then create a new project, compare to databases, generate a script and the script then starts by setting the transaction isolation level to SERIALIZABLE.

BTW, I'm using SQL Compare Pro version 10.4.8.87.

Example output:
Code:

/*
Run this script on:

        server1\instance1.database1    -  This database will be modified

to synchronize it with:

        server2\instance2.database1

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 9/5/2013 8:34:01 AM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Tue Sep 10, 2013 10:49 am    Post subject: Reply with quote

It's supposed to put this information in the registry. Maybe you don't have permissions to the key?
HKEY_CURRENT_USER\Software\Red Gate\SQL Compare 10\UI

(Value name is TransactionIsolationLevel: it's a string and the value should be what you set in the options.)
Back to top
View user's profile Send private message
JosvanDuijn



Joined: 25 Jan 2011
Posts: 9

PostPosted: Tue Sep 10, 2013 10:59 am    Post subject: Reply with quote

Found the key in my registry: TransactionIsolationLevel and it is set to 'READ COMMITTED'. So that seems fine.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Wed Sep 11, 2013 11:45 am    Post subject: Reply with quote

The most likely explanation is that SQL Compare ignores the transaction isolation level (on purpose) when you are comparing a data source that is linked to SQL Source Control. For databases that are not linked to source control, it should respect what is in the registry.
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