Red Gate forums :: View topic - Testing Sql Compare, [3910] Transaction context in use by ..
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

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

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
HLEBOEUF



Joined: 10 Feb 2006
Posts: 11

PostPosted: Fri Feb 10, 2006 1:10 pm    Post subject: Testing Sql Compare, [3910] Transaction context in use by .. Reply with quote

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
Back to top
View user's profile Send private message
Andras



Joined: 19 May 2005
Posts: 249
Location: Cambridge, UK

PostPosted: Fri Feb 10, 2006 2:49 pm    Post subject: Re: Testing Sql Compare, [3910] Transaction context in use b Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
HLEBOEUF



Joined: 10 Feb 2006
Posts: 11

PostPosted: Thu Feb 16, 2006 11:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Andras



Joined: 19 May 2005
Posts: 249
Location: Cambridge, UK

PostPosted: Thu Feb 16, 2006 4:42 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
bveenstra



Joined: 11 Jan 2008
Posts: 2

PostPosted: Fri Jan 11, 2008 4:34 pm    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message
Eric Tobias



Joined: 07 Feb 2008
Posts: 1

PostPosted: Thu Feb 07, 2008 12:06 am    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
bveenstra



Joined: 11 Jan 2008
Posts: 2

PostPosted: Thu Feb 07, 2008 2:10 am    Post subject: Reply with quote

"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? Very Happy
Back to top
View user's profile Send private message
Maxer



Joined: 30 Jun 2008
Posts: 1

PostPosted: Mon Jun 30, 2008 8:37 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Thu Nov 03, 2011 2:17 pm    Post subject: Reply with quote

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/viewtopic.php?t=14113

Kind regards,

David Atkinson
Product Manager
Red Gate Software
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group