Error - LINQ/TransactionScope with multiple database connections

Question

4.00/5 (1 vote)

See more:

, +

Hey Troops,

I'm having a helluva time wrapping a couple transactions to 2 different databases on the same SQL Server. I initially was having trouble with network DTC access and I resolved that. Now, the error that I continue to get is "Communication with the underlying transaction manager has failed."

We have some customer profiles in a database and when these profiles become outdated we want to move them to an 'archive' database for storage. The move is simply (italics for humor) adding them to the archive database and deleting them from the main/live database. I have a DataContext for each database. The code below performs the Add and then gets the error on the Delete when trying to use the second DataContext. I've only been working with LINQ for a few months and I've scoured articles for the past couple of days. I'd like to know if anything is wrong with my code or if there is still something not configured properly with the DTC or ???

We're running on VMware for my workstation and the server.
- Workstation is Windows 7 SP1
- Server is Windows and SQL Server 2008R2

Routine for the 'Move':

C#

private int MoveProfileToArchiveDB( int iProfileId )
{
    int rc = RC.UnknownError;
    // get new Archive profile object
    ProfileArchive.ProfileInfo piArchive = new ProfileArchive.ProfileInfo();
    // 'Live' DataContext
    using ( ProfileDataContext dbLive = new ProfileDataContext() )
    {
        // get Live profile
        ProfileInfo piLive = ProfileInfo.GetProfile( dbLive, iProfileId );
        // copy Live data to Archive profile object... including the id
        ProfileArchive.ProfileInfo.CopyFromLive( piLive, piArchive, true );
    }
    bool bArchiveProfileExists = ProfileArchive.ProfileInfo.ProfileExists( piArchive.id );
    // make the move a transaction... 
    using ( TransactionScope ts = new TransactionScope() )
    {
        // Add/Update to Archive db
        using ( ProfileArchiveDataContext dbArchive = new ProfileArchiveDataContext() )
        {
            // if this profile already exists in the Archive db...
            if ( bArchiveProfileExists )
            {
                // update the personal profile in Archive db
                rc = ProfileArchive.ProfileInfo.UpdateProfile( dbArchive, piArchive );
            }
            else
            {
                // add this personal profile to the archive db
                int iArchiveId = 0;
                piArchive.ArchiveDate = DateTime.Now;
                rc = ProfileArchive.ProfileInfo.AddProfile( dbArchive, piArchive, ref iArchiveId );
            }
            // if Add/Update was successful...
            if ( rc == RC.Success )
            {
                // Delete from the Live db
                using ( ProfileDataContext dbLive = new ProfileDataContext() )
                {
                    // delete the personal profile from the Profile DB
                    rc = ProfileInfo.DeleteProfileExecCmd( dbLive, iProfileId );    // *** ERROR HERE ***
                    if ( rc == RC.Success )
                    {
                        // Transaction End (completed)
                        ts.Complete();
                    }
                }
            }
        }
    }
    return rc;
}

NOTES:
1. I have a few different methods for the Delete and they all work outside the TransactionScope.
2. ProfileInfo is the main profile table and is roughly the same for both Live and Archive.

Any help is greatly appreciated! Thanks much...

Posted 26-Jun-12 15:43pm

stretchtack

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wonde Tadesse · Accepted Answer · 2012-06-26T16:18:00

Solution 1

Have a look these articles.
1.Distributed transactions and TransactionScope [how to get it?][^]
2.Entity Framework & Transactions[^]
3.TransactionScope and DataAdapters[^]
4.Handling transactions in .Net using TransactionScope[^]

Posted 26-Jun-12 16:18pm

Wonde Tadesse

Comments

stretchtack 26-Jun-12 23:00pm

Are telling me that all 4 of the articles apply to fixing my problem? Are you telling me not to use LINQ? I've spent the better part of 2 days reading... 'scouring' as I put it. I'm not using DataAdapters. I know how to do a transaction and I know how to use TransactionScope. I can't get it to work with 2 connections each to a different database.

Wonde Tadesse 27-Jun-12 17:55pm

It's hard to see the problem from your code. That is why I provide the links. In addition you didn't specified any exception occurred during the transaction.

stretchtack 27-Jun-12 18:39pm

The error I mentioned is an exception on the DeleteProfileExecCmd. The problem occurs when I try to use the second datacontext within the TransactionScope. If I remove the TransactionScope it works.

Wonde Tadesse 27-Jun-12 19:37pm

I can't see any exception that DeleteProfileExecCmd generates. It's better to provide this exception. May be try to block your code in try{}catch(Exception ex) {} block and see the exception message.

stretchtack 27-Jun-12 20:00pm

The message above in my original post - "Communication with the underlying transaction manager has failed." is the exception message. If you'd like the stack trace or similar I can post that. The try/catch is up a level in the routine that called this one.

Wonde Tadesse 27-Jun-12 20:36pm

It seems an issue of SQL Server version 2005 and less. The DataContext needs differing entity connection to be passed in the constructor. Like ProfileDataContext dbLive = new ProfileDataContext(entityConnectionInstance) ... For more see this msdn link. http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/189b2718-c2b0-4290-8517-3cd3da4314fc.

stretchtack 27-Jun-12 20:53pm

I can look at that but I'm using SQL Server 2008R2. It's not supposed to be an issue with 2008. I read plenty about older versions and their issues.

Wonde Tadesse 27-Jun-12 22:29pm

Sorry I don't have the whole solution of your project. It's hard to automate such kind of exceptions on my laptop. See the links and try to change your code accordingly. Cheers.:)

stretchtack 28-Jun-12 1:02am

True... this might be tough to recreate. It all depends on what the actual issue is. I'm not going to read any further. I'm going to move the transaction into SQL before I bang my head any further on this. I was hoping someone would shed some light on this, but after 50 views here and only 1 person posting a message... it seems clear that most don't want to touch this... including me. ;) I got other work to do. Thanks.

Error - LINQ/TransactionScope with multiple database connections

1 solution

Solution 1

Add your solution here

Preview 0

Error - LINQ/TransactionScope with multiple database connections

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us