"The underlying provider failed on open" with large spreadsheet

Question

I am using the Entity Framework to upload a large spreadsheet (14k rows) and perform updates to my databse. If I set throught the code line by line, then each update will process as it's supposed to. However, once I step out of the code I begin to see this exception being thrown: The underlying provider failed on open.

This is throwing me off because we use the entity framework throughout the site and it works fine, so I know our connection string is not the problem. In fact, this page will even handle the upload just fine when the spreadsheet is smaller. However, here it is for good measure.

<add name="AgentResources" connectionString="metadata=res://*/Models.AgentResources.csdl|res://*/Models.AgentResources.ssdl|res://*/Models.AgentResources.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=QUAHILSQ03\sql08A;initial catalog=AgentResourcesU01;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I'm using a using statement that should be disposing of the dbContext object, so I'm not sure what's going on.

Here is the method that is throwing the error. For each record, the TerritoryOnly will be called as well as one of the Save methods.

    public static string TerritoryOnly(ZipCodeTerritory zipCode, string territory)
    {
        msg = string.Empty;
        using (db = new AgentResources())
        {
            zipCode.IndDistrnId = territory;
            Save(zipCode);                
        }
        return msg;
    }

    private static void Save(ZipCodeTerritory autoTermZip, ZipCodeTerritory newZip)
    {
        db.Entry(autoTermZip).State = EntityState.Modified;
        db.SaveChanges();

        db.ZipCodeTerritory.Add(newZip);
        db.SaveChanges();
    }

    private static void Save(ZipCodeTerritory zip)
    {
        db.Entry(zip).State = EntityState.Modified;
        db.SaveChanges();
    }

EDIT

The loop that is using the static methods mentioned above is itself inside a using(var scope = new TransactionScope()) statement in order to make all changes to the database atomic. Not sure if this could be contributing to the problem or not.

score 1 · Answer 1 · answered Dec 31 '13 at 19:37

1

I infer that db is declared as static, at the class level?

You might be running into some kind of concurrency error. Static methods and ESPECIALLY static class-level variables are prone to causing problems when more than one thing is going on at the same time.

If you have to have TerritoryOnly as a static method, I would advise reworking it so that you declare db in that method, and pass it as a parameter to the methods that use it.

public static string TerritoryOnly(ZipCodeTerritory zipCode, string territory)
{
    msg = string.Empty;
    using (var db = new AgentResources())
    {
        zipCode.IndDistrnId = territory;
        Save(db, zipCode);                
    }
    return msg;
}

I would also advise setting VS so that you break on every exception thrown. That will cause the app to freeze at the initial low-level exception that ultimately causes your "The underlying provider failed on open." error. That may tell you a lot about exactly what causes it.

answered Dec 31 '13 at 19:37

Ann L.

13,760
5
35
66

Just tried making these changes and I get a little farther into the spreadsheet but only on occasion (it seems to break at right around the 240 record mark). I forgot to mention that all this work is being done inside a `using(var scope = new TransactionScope()) statement. Could that be contributing to the problem? – NealR Dec 31 '13 at 20:51
Hmm. I seem to recall that when you open more than one connection within a `TransactionScope` instance, it attempts to escalate the transaction to `MSDTC`. That's been known to cause this error. Just for diagnostic purposes, you might try it without the `TransactionScope`, see what happens. – Ann L. Dec 31 '13 at 22:09
... but, since you're doing a separate open and save on every row, you're going to be opening and closing the connection 14,000 times. It might just be that you're exhausting the connection pool, and in any case, it's not going to go very fast. Could you re-tool your code so that all 14,000 updates use the same `AgentResources` instance? (For preference, not making it static but using constructor injection.) – Ann L. Dec 31 '13 at 22:12
Finally: you've probably looked at this, but just in case: lots of reasons (and solutions) for this error message: http://stackoverflow.com/questions/2475008/the-underlying-provider-failed-on-open?rq=1 – Ann L. Dec 31 '13 at 22:14
Thanks for the assistance. I tried the loop without the `TransactionScope` and I no longer get the error so the problem/possible solution is somewhere in that area... – NealR Jan 06 '14 at 20:51
What version of SQL Server are you using? If it's below SQL2008, opening more than one connection (even to the same datasource) will cause the transaction to escalate to MSDTC, which you may not have enabled. Here's a description: http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines. And the escalation to MSDTC, if you have that turned off, will definitely cause the error you're seeing. – Ann L. Jan 07 '14 at 12:53

"The underlying provider failed on open" with large spreadsheet

1 Answers1