Setting for transaction isolation level not used in script

JosvanDuijn

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

Brian Donahue

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.

JosvanDuijn

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:

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

Brian Donahue

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

JosvanDuijn

Found the key in my registry: TransactionIsolationLevel and it is set to 'READ COMMITTED'. So that seems fine.

Brian Donahue

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.

MrSuaveh

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?

MurrayTaylor

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.

MrSuaveh

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?