none
MultipleActiveResultSets error using SQL Server 2000 RRS feed

  • Question

  • I have an ASP.NET 2.0 application that occasionally shows this error:

     

    The connection does not support MultipleActiveResultSets.

     

    I am using Visual Studio 2005 and SQL Server 2000.  I realize that this error occurs with SQL Server 2005, and every post I have seen related to SQL Server 2005 says, "set the connection string to MultipleActiveResultSets=true".

     

    Again, I am using SQL Server 2000, not 2005.  I have set the connection string to MultipleActiveResultSets=true, back to false, taken it out, it seems to make no difference.  If I stop the process "w3wp.exe", I can reload the page and not see the error.  A day or so later, I launch the application and I get this error again.  I am sure the error relates to an open connection or something, and Visual Studio 2005 ASSUMES I am using SQL Server 2005, so it throws this error, but I am not using SQL Server 2005, so it doesn't help.

     

    Any suggestions on what the REAL error might be, and what to do about it?

     

     

     

     

     

    Tuesday, November 20, 2007 5:02 PM

Answers

  • What it means is a single connection to SQL Server only supports a single pending action at a time.

     

    For example if you have a single SqlConnection if you try to open 2 SqlDataReaders on this connection without closing the first one you will get this error.

     

    With SQL 2000 you need to be careful and only do one thing at a time on the SqlConnection since SQL 2000 does not support MARs.

    Monday, March 24, 2008 9:49 PM

All replies

  • SQL 2000 does not support MARS (Multiple Active Result Sets).

     

    This means that you cannot simultaneously loop through the results of several SQL commands on the same Connection.

    Wednesday, November 21, 2007 2:36 AM
  •  BinaryCoder wrote:

    SQL 2000 does not support MARS (Multiple Active Result Sets).

     

    This means that you cannot simultaneously loop through the results of several SQL commands on the same Connection.

     

    And if I am not TRYING to simultaneously loop through multiple results, what is the real error?  The closest error I can guess is occurring that that a recordset is not closed, which is an error I occasionally get as well.  I just wish Microsoft could have found a way to throw one error when you are using SQL Server 2005 and a different (more helpful) error when you are using SQL Server 2000.

    Monday, March 24, 2008 11:26 AM
  • What it means is a single connection to SQL Server only supports a single pending action at a time.

     

    For example if you have a single SqlConnection if you try to open 2 SqlDataReaders on this connection without closing the first one you will get this error.

     

    With SQL 2000 you need to be careful and only do one thing at a time on the SqlConnection since SQL 2000 does not support MARs.

    Monday, March 24, 2008 9:49 PM
  •  Matt Neerincx wrote:

    What it means is a single connection to SQL Server only supports a single pending action at a time.

     

    For example if you have a single SqlConnection if you try to open 2 SqlDataReaders on this connection without closing the first one you will get this error.

     

    With SQL 2000 you need to be careful and only do one thing at a time on the SqlConnection since SQL 2000 does not support MARs.

     

    Matt,

     

      Thanks, I thought it was something like that.  Prior to SQL 2005, I would see errors like "There is already an open DataReader associated with this Command which must be closed first.", which is a lot more intuitive and tells me what to look for.  I _know_ that you can only use one data reader at a time with a (SQL Server 2000) connection, so I code around that.  Since SQL 2000 does not support MARS, the error message is confusing to someone who isn't familiar with SQL 2005 (like me, until recently) and wasn't even trying to use MARS.

    Tuesday, March 25, 2008 11:22 AM
  • Yes I guess from the driver's perspective it can talk to SQL 2000 and SQL 2005.  SQL 2000 does not support MARS and SQL 2005 does.  I'll file a bug to revise the error message so it will be more clear here, thanks for your feedback.

     

    Tuesday, March 25, 2008 3:08 PM