"context connection" and MultipleActiveResultSets ...Can have both at the same time?

Hi,

When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:

System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.

Can we have MARS in the "context connection"?

note: I'm doing this to support multiple open datareaders in a CLR stored procedure.

Thanks!

Andy

While I could not find explicit documentation on WHY, from my tests you CANNOT leverage MARS for inprocess SQL Server connections. Since you cannot append the MARS= in the connection string I simply tried to use the feature hoping context connections would allow it...

I ran this code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void usp_MARS()

{

string strSQLGetOrder = "Select * from Sales.SalesOrderDetail WHERE SalesOrderID = 43659";

string strSQLUpdateInv = "UPDATE Production.ProductInventory SET Quantity=Quantity-@.amt WHERE (ProductID=@.pid)";

SqlConnection marsConnection = new SqlConnection("context connection=true");

marsConnection.Open();

SqlCommand readCommand = new SqlCommand(strSQLGetOrder, marsConnection);

SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv, marsConnection);

writeCommand.Parameters.Add("@.amt", SqlDbType.Int);

writeCommand.Parameters.Add("@.pid", SqlDbType.Int);

using (SqlDataReader rdr = readCommand.ExecuteReader())

{

while (rdr.Read())

{

writeCommand.Parameters["@.amt"].Value = rdr["OrderQty"];

writeCommand.Parameters["@.pid"].Value = rdr["ProductID"];

writeCommand.ExecuteNonQuery();

}

}

marsConnection.Close();

}

};

And I receive the following runtime error on execute of the proc:

Msg 6522, Level 16, State 1, Procedure usp_MARS, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_MARS':

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

System.InvalidOperationException:

at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)

at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at StoredProcedures.usp_MARS()

|||

MARS is not supported for server-side CLR code. However, you can use cursors to simulate similar kinds of behavior in some circumstances. See the ResultSet sample for a fairly painless way to do that. The latest samples MSI is located at http://msdn.microsoft.com/sql/downloads/samples/default.aspx. After you install the Samples MSI, by default you'll find the ResultSet sample at drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\ResultSet. There is a readme file in that folder which explains how to compile the CLR code, create the database objects, and run the sample.

|||

Hi Andy!

MARS is not available with context connections in SQL Server 2005.

Of cause, you can always use out-of-proc connection (where MARS can be enabled) to own server from CLR UDP.

|||Thanks!