Testing Sql Compare, [3910] Transaction context in use by ..

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

Testing Sql Compare, [3910] Transaction context in use by ..

Postby HLEBOEUF » Fri Feb 10, 2006 1:10 pm

In the first comparison i'm making between 2 databases i'm getting an error [3910] Transaction context in use by another session when synchronizing.
Any suggestion on how to get it working.

Harry Leboeuf
HLEBOEUF
 
Posts: 11
Joined: Fri Feb 10, 2006 1:04 pm

Re: Testing Sql Compare, [3910] Transaction context in use b

Postby Andras » Fri Feb 10, 2006 2:49 pm

HLEBOEUF wrote:In the first comparison i'm making between 2 databases i'm getting an error [3910] Transaction context in use by another session when synchronizing.
Any suggestion on how to get it working.

Harry Leboeuf


Hi,
are you using SQL Server 2000 or 2005? What I suspect the problem is that you have a linked server which is referred to in one of your database objects. In this case the transaction produced by SQL Compare is upgraded to a distributed transaction. SQL Server 2005 is much stricter about these, and accepts less. Were you able to find which object is causing the problem?

Andras
András Belokosztolszki, PhD
Red Gate Software Ltd.
Andras
 
Posts: 249
Joined: Thu May 19, 2005 5:07 pm
Location: Cambridge, UK

Postby HLEBOEUF » Thu Feb 16, 2006 11:03 am

I'm using 2005 and yes, you're right i'm using linked servers in my stored procedures.
Will they be a way the SqlCompare product will work arround it ?
Because it dosn't make big sense when i'm not able to synchronize all objects.
HLEBOEUF
 
Posts: 11
Joined: Fri Feb 10, 2006 1:04 pm

Re:

Postby Andras » Thu Feb 16, 2006 4:42 pm

HLEBOEUF wrote:I'm using 2005 and yes, you're right i'm using linked servers in my stored procedures.
Will they be a way the SqlCompare product will work arround it ?
Because it dosn't make big sense when i'm not able to synchronize all objects.


Unfortunately this is a limitation of SQL Server. You could try one of the following. You could try to lower the transaction isolation level in the script generated by SQL Server. In the generated script we use serializable. If you change this to read commited, it might help (it relaxes the requirements for the distributed transaction). So you need to include:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Another alternative is that if you are confident that the transaction will succeed, and there are no strange dependency problems, you may use the NoPlumbing option in SQL Compare's options. Note, that when this option is selected, the synchronization is not executed in a transaction, thus rollback does not work.

Regards,
Andras
András Belokosztolszki, PhD
Red Gate Software Ltd.
Andras
 
Posts: 249
Joined: Thu May 19, 2005 5:07 pm
Location: Cambridge, UK

Re:

Postby bveenstra » Fri Jan 11, 2008 4:34 pm

Andras wrote:So you need to include:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Unfortunately that did not work for our 2005 environment either.

Isn't there any way for SQLCompare to "test" this workaround for Linked Servers during startup/new project?


Andras wrote:Another alternative is that if you are confident that the transaction will succeed, and there are no strange dependency problems, you may use the NoPlumbing option in SQL Compare's options. Note, that when this option is selected, the synchronization is not executed in a transaction, thus rollback does not work.

Could you make a distinct option for SQLCompare to use Transactions where it can, and exclude transactions on items containing Linked Servers? That would be the most elegant solution in my mind. Perhaps different color coding on the Compare results grid?
bveenstra
 
Posts: 2
Joined: Fri Jan 11, 2008 4:13 pm

Re:

Postby Eric Tobias » Thu Feb 07, 2008 12:06 am

Andras wrote:Unfortunately this is a limitation of SQL Server. You could try one of the following. You could try to lower the transaction isolation level in the script generated by SQL Server. In the generated script we use serializable. If you change this to read commited, it might help (it relaxes the requirements for the distributed transaction). So you need to include:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Another alternative is that if you are confident that the transaction will succeed, and there are no strange dependency problems, you may use the NoPlumbing option in SQL Compare's options. Note, that when this option is selected, the synchronization is not executed in a transaction, thus rollback does not work.

Regards,
Andras


I've run into this problem as well. I tried lowering the transaction isolation level but that didn't work. I don't see any "NoPlumbing" option in the GUI anywhere. Where should I be looking and/or are there any other options?

I just purchased the toolbelt and this is my first compare project.
Eric Tobias
 
Posts: 1
Joined: Thu Feb 07, 2008 12:03 am

Postby bveenstra » Thu Feb 07, 2008 2:10 am

"no plumbing" I believe is a reference to a command-line option... what you're looking for is to turn on "Do not use transactions in synchronization scripts" ... it feels like a double-negative, does it not? :D
bveenstra
 
Posts: 2
Joined: Fri Jan 11, 2008 4:13 pm

Postby Maxer » Mon Jun 30, 2008 8:37 pm

I ran into the same issue.

Stored procedure that references a linked server...

I saw (here or in the knowledge base I forget now) that it is due to the transaction level and being a linked server.

The only way I could get it to work was to adjust the project options so that it would not use transactions in synch scripts.

I know that it was stated this would NOT be changed going forward, and I understand that.

Using a transaction for these types of changes is important so you don't leave your database in a potentially messed up unknown state.

HOWEVER, that being said, is there any way to make this easier for the end user?

Perhaps it could compare table names against the linked server list and warn the end user during the compare process that these sprocs, functions, whatever reference a linked server.

Due to that it will not be able to use a transaction to commit the changes.

Perhaps it could offer to do all the changes that are not related to linked servers (if I have 10 sprocs and 3 of them use linked servers it could do the 7 of the 10) ?

Then it could offer to go back, NOT use transactions, and update the database for each of the sprocs or functions that use a linked server. Performing the update one by one and appending it to the saved script file in some way.

Basically I'd like to be able to make the change in one "session" so to speak so that when I review the change script I can see that all these changes were made.

However, perhaps SQL Compare could perform each change one by one without transactions enabled and then notify if one of the changes fails and then offer to abort the process (or mark it as failed and continue... which wouldn't seem overly wise though I suppose).

My current work around is to have two versions of the project one with "Do Not use transaction in synchronization scripts" checked and the other not checked (and a comment behind each to say which is which).

Anything to streamline this into a single project and provide a script history for documentation purposes would be great.

(Perhaps SQL Compare could offer to backup the schema of the object being changed so that in case the change fails it can roll back the structure at least?)

Not sure if that makes sense, but as I said, anything to help integrate this issue into the natural flow of the program would be greatly appreciated.

I can't imagine this is a rare problem as so many people appear to use the Linked Servers feature of SQL Server.

Thank you.
Maxer
 
Posts: 1
Joined: Mon Jun 30, 2008 8:24 pm

Postby David Atkinson » Thu Nov 03, 2011 2:17 pm

In SQL Compare 9.5's application options there is a setting to set the transaction isolation level, which may help in this scenario. For more details, see:

http://www.red-gate.com/MessageBoard/vi ... hp?t=14113

Kind regards,

David Atkinson
Product Manager
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests