M
Mike Kiefer
I originally started having problems with this in a different application
but after scouring every bit of code for missing connection Close()
statements or Dispose() statements, I was convinced that this had to be a
bug that only showed up with with both Sql Debugging on and connection
pooling in use. The following app will reproduce the problem consistently as
indicated in the comments.
Hopefully someone from Microsoft will pick this up and put it to good use in
order to solve the problem in the next release.
Mike
////////////////////////////////////////////////////////////////////////////
/
/// Under the proper conditions, this program will repeatedly generate the
/// following error after 100 iterations:
///
/// Timeout expired. The timeout period elapsed prior to obtaining a
/// connection from the pool. This may have occurred because all pooled
/// connections were in use and max pool size was reached.
///
/// This error will occur ONLY if SQL debugging is enabled under the project
/// settings (Configuration Properties->Debugging->Enabled SQL Debugging =
true)
/// AND connection pooling is enabled (by default or if "pooling=true"
/// in the connection string). If EITHER of these options are turned off,
the
/// program successfully completes all 500 iterations. Otherwise, the error
will occur
/// at the start of iteration #100.
///
/// Tested using SQL Server 2000 SP3, Visual Studio .NET 2003, and
/// the .NET Framework version 1.1. The problem originally appeared prior to
/// installing SQL Server SP3, so other combinations may also be a problem.
////////////////////////////////////////////////////////////////////////////
/
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlDebuggingTest2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
try
{
for (int i = 0 ; i < 500; i++)
{
Console.Out.WriteLine("Iteration # {0}", i);
SqlConnection conn = new
SqlConnection("pooling=true;Server='(local)';Integrated
Security=true;Initial Catalog=northwind;Application Name=Testing;");
try
{
conn.Open();
SqlCommand cmdToExecute = new SqlCommand();
try
{
cmdToExecute.CommandText = "CustOrdersOrders";
cmdToExecute.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
try
{
cmdToExecute.Connection = conn;
string customerId = "QUICK";;
cmdToExecute.Parameters.Add(new SqlParameter("@CustomerID",
customerId));
DataSet ds = new DataSet("foo");
try
{
adapter.Fill(ds);
}
finally
{
if (ds != null)
ds.Dispose();
}
}
finally
{
if (adapter != null)
adapter.Dispose();
}
}
finally
{
if (cmdToExecute != null)
cmdToExecute.Dispose();
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("{0}", ex.ToString());
Console.WriteLine("Program terminated."); // line exists to set
breakpoint on.
}
}
}
}
but after scouring every bit of code for missing connection Close()
statements or Dispose() statements, I was convinced that this had to be a
bug that only showed up with with both Sql Debugging on and connection
pooling in use. The following app will reproduce the problem consistently as
indicated in the comments.
Hopefully someone from Microsoft will pick this up and put it to good use in
order to solve the problem in the next release.
Mike
////////////////////////////////////////////////////////////////////////////
/
/// Under the proper conditions, this program will repeatedly generate the
/// following error after 100 iterations:
///
/// Timeout expired. The timeout period elapsed prior to obtaining a
/// connection from the pool. This may have occurred because all pooled
/// connections were in use and max pool size was reached.
///
/// This error will occur ONLY if SQL debugging is enabled under the project
/// settings (Configuration Properties->Debugging->Enabled SQL Debugging =
true)
/// AND connection pooling is enabled (by default or if "pooling=true"
/// in the connection string). If EITHER of these options are turned off,
the
/// program successfully completes all 500 iterations. Otherwise, the error
will occur
/// at the start of iteration #100.
///
/// Tested using SQL Server 2000 SP3, Visual Studio .NET 2003, and
/// the .NET Framework version 1.1. The problem originally appeared prior to
/// installing SQL Server SP3, so other combinations may also be a problem.
////////////////////////////////////////////////////////////////////////////
/
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlDebuggingTest2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
try
{
for (int i = 0 ; i < 500; i++)
{
Console.Out.WriteLine("Iteration # {0}", i);
SqlConnection conn = new
SqlConnection("pooling=true;Server='(local)';Integrated
Security=true;Initial Catalog=northwind;Application Name=Testing;");
try
{
conn.Open();
SqlCommand cmdToExecute = new SqlCommand();
try
{
cmdToExecute.CommandText = "CustOrdersOrders";
cmdToExecute.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
try
{
cmdToExecute.Connection = conn;
string customerId = "QUICK";;
cmdToExecute.Parameters.Add(new SqlParameter("@CustomerID",
customerId));
DataSet ds = new DataSet("foo");
try
{
adapter.Fill(ds);
}
finally
{
if (ds != null)
ds.Dispose();
}
}
finally
{
if (adapter != null)
adapter.Dispose();
}
}
finally
{
if (cmdToExecute != null)
cmdToExecute.Dispose();
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("{0}", ex.ToString());
Console.WriteLine("Program terminated."); // line exists to set
breakpoint on.
}
}
}
}