Setting for transaction isolation level not used in script

Forum for users of SQL Compare schema synchronization utility

Setting for transaction isolation level not used in script

Postby JosvanDuijn » Thu Sep 05, 2013 8:00 am

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
JosvanDuijn
 
Posts: 9
Joined: Tue Jan 25, 2011 10:36 am

Postby Brian Donahue » Mon Sep 09, 2013 11:44 am

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.
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Postby JosvanDuijn » Mon Sep 09, 2013 12:15 pm

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: Select all
/*
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
JosvanDuijn
 
Posts: 9
Joined: Tue Jan 25, 2011 10:36 am

Postby Brian Donahue » Tue Sep 10, 2013 10:49 am

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.)
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Postby JosvanDuijn » Tue Sep 10, 2013 10:59 am

Found the key in my registry: TransactionIsolationLevel and it is set to 'READ COMMITTED'. So that seems fine.
JosvanDuijn
 
Posts: 9
Joined: Tue Jan 25, 2011 10:36 am

Postby Brian Donahue » Wed Sep 11, 2013 11:45 am

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.
Brian Donahue
 
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Re: Setting for transaction isolation level not used in scri

Postby MrSuaveh » Tue Apr 21, 2015 7:21 am

I am having the same problem.

I changed the Transaction Isolation Level to 'Read Uncommitted' in Application Options. I see 'Read Uncommited' as the TransactionIsolationLevel in the registry. I am comparing two SQL 2012 databases that are NOT under Source Control. However SQL Compare 10 UI creates the deployment script with 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'.

What is the purpose of the 'Transaction Isolation Level' in Tools, Application Options?
MrSuaveh
 
Posts: 3
Joined: Mon Jul 07, 2014 7:36 pm

Re: Setting for transaction isolation level not used in scri

Postby MurrayTaylor » Sat Jun 27, 2015 8:17 am

To set the isolation level, you can issue a "SET Transaction Isolation Level statement" after you connect to SQL Server. The isolation level will apply to the rest of that session, unless you explicitly change the level again.

Within the SET Transaction Isolation Level statement, you must specify one of the isolation level.
Sub Keh Do.
MurrayTaylor
 
Posts: 1
Joined: Sat Jun 27, 2015 7:55 am

Re: Setting for transaction isolation level not used in scri

Postby MrSuaveh » Tue Jun 30, 2015 6:11 pm

MurrayTaylor wrote:To set the isolation level, you can issue a "SET Transaction Isolation Level statement" after you connect to SQL Server. The isolation level will apply to the rest of that session, unless you explicitly change the level again.

Within the SET Transaction Isolation Level statement, you must specify one of the isolation level.


@MurrayTaylor Can you please explain how connecting to SQL Server will affect the 'SET Transaction Isolation Level' statement generated in the RedGate Migration script?
MrSuaveh
 
Posts: 3
Joined: Mon Jul 07, 2014 7:36 pm


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: Google [Bot] and 0 guests