I've got doubt about MARS feature support in ASP.NET 2.0 and MSSQL 2.0

[Optimus]

Hi everyone,

I've got issue about MARS feature support in ASP.NET 2.0 and MSSQL 2.0
How to get things work?

What i've done is I got vs.net 2.0 and MSSQL 2005 installed on my pc.
Then I tried to test MARS feature following the example in the msdn
website
as the url "http://msdn2.microsoft.com/en-us/library/yf1a7f4f"
to see what the asp.net 2.0 can do with multiple active result sets.

What the code should be doing is to instantiate two instances of
datareader.
Then it will execute the datareader#1 and loop through the first result
set returned.
after that inside the datareader#1's block, the code will get the
datareader#2 executed and retrieve its own result sets while the first
datareader's retrieving operation is not completed.

When running the code,I still got the error "There is already an open
DataReader associated with this Command which must be closed first."
which is the same error I should encounter with the older version of
ASP.NET.

Because it should work fine as long as I got everything installed
correctly.

 

[Patrice]

Not enabled in the connection string ?

You mention SQL Server 2005 but also MSSQL 2.0 (do you mean 2000 ?). Which
version are you using ? If you have both double check you are trying against
SQL Server 2005 (from the previous page it looks like you need SQL Server
2005).

 

[connectingvivek]

Buddy u gotta take care of couple of things here:
1. Mars works only with SQL 2005 and above.

2.Parallel Execution
MARS is not designed to remove all requirements for multiple connections in an application. If an application needs true parallel execution of commands against a server, multiple connections should be used.

For example, consider the following scenario. Two command objects are created, one for processing a result set and another for updating data; they share a common connection via MARS. In this scenario, the Transaction.Commit fails on the update until all the results have been read on the first command object, yielding the following exception:

Message: Transaction context in use by another session.

There are three options for handling this scenario:

Start the transaction after the reader is created, so that it is not part of the transaction. Every update then becomes its own transaction.

Commit all work after the reader is closed. This has the potential for a substantial batch of updates.

Don't use MARS; instead use a separate connection for each command object as you would have before MARS.