Setting the transaction isolation level

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Setting the transaction isolation level

Postby zacuke » Wed Jun 04, 2014 3:55 pm

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?

Code: Select all
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]
zacuke
 
Posts: 3
Joined: Tue Jan 15, 2013 5:47 pm

Postby Brian Donahue » Thu Jun 05, 2014 4:24 pm

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


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests