SQL Azure + Getting an error 'There is already an open DataReader associated with this Command..', even after setting ‘MultipleActiveResultSets=True’

Question

We are facing some issues with the deployed version of our application on cloud.

Our application is an ASP.NET MVC 3 project with ADO .NET Entity Framework.

According to the msdn blogs, we need to add the parameter ‘MultipleActiveResultSets=True’ (for Entity Framework with SQL azure) in our database connection string which we have rightly done.

We get the following exception while executing a database query from our application: ‘’There is already an open DataReader associated with this Command which must be closed first.”, which I guess is very much related to the ‘MultipleActiveResultSets=True’ property.

The application works fine on the local environment with our locally deployed database as well as using the SQL azure database (on local environment).

But when we deploy our application of cloud we get the above mentioned error. It seems that ‘MultipleActiveResultSets=True’ property is not being read from the connection string.

The connection string that I am using is as follows:

    <connectionStrings>
        <add name="#DBInstanceName#" connectionString="Data Source=tcp:#server#.database.windows.net,1433;Initial Catalog=#DBName#;User ID=”UserName#@#server#;Password=”#password#”;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

I am able to connect to SQL Azure database using the above connection string from the locally deployed application without any exceptions. But the same application when deployed on cloud using the same connection string gives me the above mentioned error.

Can you please help us fixing the same?

Answer 1

We're on the new Azure portal and had this problem as well. MARS worked on all local dev environments, but threw the same nasty exception on Azure. We verified that the connection string in the physical web.config file deployed to Azure contained MultipleActiveResultSets=true; but it didn't seem to be honored by the application. Ultimately what worked was going to the app service instance > settings > data connections, and deleting the data connection of the same name as the one in question in the web.config. My guess is that the one that Azure stores trumps the one in the deployed web.config.

Answer 2

Alternatively you can add the "MultipleActiveResultSets=true" to your connection string and update the destination web.config when you are using the Publish feature in Visual Studio. Make sure to select "Use this connection string at runtime (update destination web.config)."

Answer 3

i have the same problem, but i solved MultipleActiveResultSets=True in the connection string from Publish assistant, as irium

Answer 4

You do have to set multipleresultsets=true in connectionsstring and restart.

Removed answer

Edit: This is debated! Check comment beneath... I havent had time to investigate...

I think it is already turned on.

I couldn't find a spot-on source but Faisal Mohamood(Program Manager, Entity Framework) states the following back in 2010: "Needless to say, we will make sure that MARS is automatically turned ON for SQL Azure based apps when we get around to releasing the next update to the product." Found it here: http://blogs.msdn.com/b/adonet/archive/2010/06/09/remember-to-re-enable-mars-in-your-sql-azure-based-ef-apps.aspx

Answer 5

I've just got the same issue. I has tried setting "MultipleActiveResultSets=True" in Web.config connection - no results. Finally I've resolved issue by manually editing Connection string directly at Azure Dashboard for the web site - in "Configure" tab there is "Connection strings" settings where you should manually add the "MultipleActiveResultSets=True;" string. After doing that I've got MARS worked finally.

Answer 6

Try assigning MultipleActiveResultSets in code. Is it not read or is is not working?

SqlConnectionStringBuilder.MultipleActiveResultSets