Setting the transaction isolation level

zacuke

How does one tell SQL Compare SDK to use a different transaction isolation level?

I am fighting with the error "Transaction context in use by another session." There is a setting in the SQL Compare GUI, but how do I use that setting in the SDK?

How do I ensure the setting is applied when the code is ran on a computer that does not have the GUI installed?

Possibly I could manually String.Replace the script before executing, but that is not apparent how to perform the string replacement either.

EDIT: I was able to figure out how to modify the script, but is this the best way to do this?

using (var block = work.ExecutionBlock)
            {
                String myscript = block.GetString();

                myscript = myscript.Replace("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

                using (var myblock = BlockLoader.LoadFromString(myscript))
                {
                    if (settings.SqlServerUserName == "")
                    {
                        blockExecutor.ExecuteBlock(myblock, stagingDBConnectionProperties.ServerName, stagingDBConnectionProperties.DatabaseName);
                    }
                    else
                    {
                        blockExecutor.ExecuteBlock(myblock, stagingDBConnectionProperties.ServerName, stagingDBConnectionProperties.DatabaseName, false, stagingDBConnectionProperties.UserName, stagingDBConnectionProperties.Password);
                    }
                }
            }

[/code]

Brian Donahue

There is a "TransactionIsolationLevel" property on the "Work" object that you create to do the scripting. Have you tried setting that property to "READ UNCOMMITTED"?