ASP.NET LINQ SQL DATABASE

Question

I'm in the process of making live a .net system. I am using LINQ and MVC. I had to create the live database and hoped that this would run smoothly however it did not.

New SQL SERVER - Microsoft Windows NT 5.0 (2195) / 8.00.760

I created an administrator user who can add/edit/delete. Basically I receive the following error if I try to add (.InsertOnSubmit) or delete (.DeleteOnSubmit) any rows however not when I edit.

"Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."

I've Googled this error and found people believe its to do with "MSDTC service" however this seems to be ticked on the service. I have logged into the database by SQL Server Management and this user can add/delete.

One Example:

Controller
if (_service.AddAccess(access)) return RedirectToAction("Access");

public Repository()
{
    _db = new DataClassDataContext(ConfigurationManager.ConnectionStrings["RegistrarsServices"].ConnectionString);
}

public void Save()
{
    _db.SubmitChanges();
}

public bool AddAccess(Access access)
{
    try
    {
        using (var scope = new TransactionScope())
        {
            _db.Accesses.InsertOnSubmit(access);
            Save();

            scope.Complete();
        }
        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

*Please note this did work when using the development server. Microsoft Windows NT 5.2 (3790) / 10.0.1600.22

Controller

private readonly ServiceAdministration _service = new ServiceAdministration();

public ActionResult AddAccess()
{
    return View(new EmailViewModel());
}

[HttpPost]
public ActionResult AddAccess(EmailViewModel emailViewModel)
{
    if (emailViewModel.Button == "Back") return RedirectToAction("Access");

    if (!ModelState.IsValid) return View(emailViewModel);

    Access access = new Access();
    access.emailAddress = emailViewModel.emailAddress;

    if (_service.AddAccess(access)) return RedirectToAction("Access");

    emailViewModel.errorMessage = "An error has occurred whilst trying to grant access. Please try again later.";
    return View(emailViewModel);
}

Service

readonly Repository _repository = new Repository();

public bool AddAccess(Access access)
{
    return _repository.AddAccess(access);
}

Don't really know what I am missing.

Thanks in advance for any help.

Clare :-)

Answer 1

Do you have any idea why are you triggering distributed transactions? This is what you need to investigate. Usual culprit is multiple ADO.Net connection from a single TransactionScope. See ADO.NET and System.Transactions and ADO.NET and LINQ to SQL. Make sure you use a single connection (ie. LINQ2SQL context) in a transaction scope. You shouldn't have to use more than one per HTTP call anyway.