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

HLEBOEUFHLEBOEUF Posts: 18
edited November 3, 2011 9:17AM in SQL Compare Previous Versions
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
· Share on Twitter

Comments

  • Andras BAndras B Posts: 249
    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.
    · Share on Twitter
  • HLEBOEUFHLEBOEUF Posts: 18
    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.
    · Share on Twitter
  • Andras BAndras B Posts: 249
    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.
    · Share on Twitter
  • bveenstrabveenstra Posts: 2
    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?
    · Share on Twitter
  • Eric TobiasEric Tobias Posts: 1
    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.
    · Share on Twitter
  • bveenstrabveenstra Posts: 2
    "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
    · Share on Twitter
  • MaxerMaxer Posts: 1
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.