I'm trying to call out to a stored procedure asynchronously with multiple calls on the one connection via ADO.Net SqlCommand
.
The calls are being triggered on a timer every half a second and for some of the time I am receiving the results back as expected and sometimes I am receiving the following error:
System.Data.SqlClient.SqlException (0x80131904): A severe error occurred on the current command. The results, if any, should be discarded.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception , Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run Behavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asy ncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult )
The SQL logs is showing the following error repeatedly:
The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.
My connection string has MARS and Async=true set. I'm currently using SQL Server 2008 Express although the target client will be a fully fledged SQL Server instance.
I created the following console app which is exhibiting the same behaviour on my machine, the DummySp
I created just returns as soon as its called
public class BusinessObject
{
public string Name {get; set;}
public void UpdateData(DataTable dataTable)
{
Console.WriteLine("{0}: new data received.",Name);
}
}
public class Program
{
private const string SpName = "DummySp";
private const string ConnectionsString = @"Data Source=(local)\sqlexpress;Initial Catalog=Test;Integrated Security=SSPI;Connection Timeout=3600";
private static readonly object DbRequestLock = new object();
private static readonly ManualResetEvent DatabaseRequestsComplete = new ManualResetEvent(false);
private static int _databaseRequestsLeft;
private static Timer _timer;
static readonly List<BusinessObject> BusinessObjects = new List<BusinessObject>
{
new BusinessObject{Name = "A"},
new BusinessObject{Name = "B"},
new BusinessObject{Name = "C"},
};
static void Main(string[] args)
{
_timer = new Timer(DoQuery, null, 0, 500);
Console.ReadLine();
_timer.Dispose();
}
private static void DoQuery(object state)
{
try
{
lock (DbRequestLock)
{
DatabaseRequestsComplete.Reset();
_databaseRequestsLeft = BusinessObjects.Count;
var builder = new SqlConnectionStringBuilder(ConnectionsString)
{
AsynchronousProcessing = true,
MultipleActiveResultSets = true
};
using (var connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
foreach (var businessObject in BusinessObjects)
{
var command = new SqlCommand(SpName, connection) { CommandType = CommandType.StoredProcedure };
command.BeginExecuteReader(Callback, new Tuple<SqlCommand, BusinessObject>(command, businessObject));
}
// need to wait for all to complete before closing the connection
DatabaseRequestsComplete.WaitOne(10000);
connection.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("Following error occurred while attempting to update objects: " + ex);
}
}
private static void Callback(IAsyncResult result)
{
try
{
var tuple = (Tuple<SqlCommand, BusinessObject>)result.AsyncState;
var businessObject = tuple.Item2;
using (SqlCommand command = tuple.Item1)
{
using (SqlDataReader reader = command.EndExecuteReader(result))
{
using (var table = new DataTable(businessObject.Name))
{
table.Load(reader);
businessObject.UpdateData(table);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
// decrement the number of database requests remaining and, if there are 0 fire the mre
if (Interlocked.Decrement(ref _databaseRequestsLeft) == 0)
{
DatabaseRequestsComplete.Set();
}
}
}
}
Any ideas on how to overcome this?
Thanks