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!