Setting for transaction isolation level not used in script

JosvanDuijnJosvanDuijn Posts: 14
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.)
  • Found the key in my registry: TransactionIsolationLevel and it is set to 'READ COMMITTED'. So that seems fine.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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?
  • 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.
  • 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?
Sign In or Register to comment.