Setting for transaction isolation level not used in script

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

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: 6670
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: 6670
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: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: franckdumps and 2 guests