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':
private int MoveProfileToArchiveDB( int iProfileId )
{
int rc = RC.UnknownError;
ProfileArchive.ProfileInfo piArchive = new ProfileArchive.ProfileInfo();
using ( ProfileDataContext dbLive = new ProfileDataContext() )
{
ProfileInfo piLive = ProfileInfo.GetProfile( dbLive, iProfileId );
ProfileArchive.ProfileInfo.CopyFromLive( piLive, piArchive, true );
}
bool bArchiveProfileExists = ProfileArchive.ProfileInfo.ProfileExists( piArchive.id );
using ( TransactionScope ts = new TransactionScope() )
{
using ( ProfileArchiveDataContext dbArchive = new ProfileArchiveDataContext() )
{
if ( bArchiveProfileExists )
{
rc = ProfileArchive.ProfileInfo.UpdateProfile( dbArchive, piArchive );
}
else
{
int iArchiveId = 0;
piArchive.ArchiveDate = DateTime.Now;
rc = ProfileArchive.ProfileInfo.AddProfile( dbArchive, piArchive, ref iArchiveId );
}
if ( rc == RC.Success )
{
using ( ProfileDataContext dbLive = new ProfileDataContext() )
{
rc = ProfileInfo.DeleteProfileExecCmd( dbLive, iProfileId );
if ( rc == RC.Success )
{
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...