Suddenly this week, I've started getting this error message:

System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery
requires an open and available Connection. The connection's current state is
connecting.

I'm very puzzled since I'm not calling "ExecuteNonQuery" and I am calling
"Open", which I've always assumed was Synchronous, i.e. would not return
until either A) the connection was open or B) there was an error. Here is
effectively what I am doing:

SqlConnection SourceServer = new SqlConnection(SourceConnectionString);
SourceServer.Open();

SqlCommand sourcecmd = new SqlCommand();
sourcecmd.Connection = SourceServer;
sourcecmd.CommandType = CommandType.StoredProcedure;
sourcecmd.CommandText = "sp" + TableName + "Select";
sourcecmd.CommandTimeout = 1800;
sourcecmd.Parameters.Add("myid", SqlDbType.Int).Value = MyId;
SqlDataReader read = sourcecmd.ExecuteReader();

It's on that ExecuteReader that this error is happening. Anyone else
experiencing this? And what have you done to solve this?

-BKN

Dear Bryce,

I tried the same code as yours in Windows XP SP2, C# 2.0, SQL Server 2005
and it worked fine. As it has stopped all of a sudden some of the things
that could have caused this issue are (These are just my guesses)
1) Multiple connections/connection pooling issues
2) Any other process/thread doing the same

Also if you check the state of the connection just before the
ExecuteReader(), what do you get?

--
Hope this helps!

Y. Sivaram


"Bryce K. Nielsen" <(E-Mail Removed)> wrote in message
news:%23$(E-Mail Removed)...
> Suddenly this week, I've started getting this error message:
>
> System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery
> requires an open and available Connection. The connection's current state
> is
> connecting.
>
> I'm very puzzled since I'm not calling "ExecuteNonQuery" and I am calling
> "Open", which I've always assumed was Synchronous, i.e. would not return
> until either A) the connection was open or B) there was an error. Here is
> effectively what I am doing:
>
> SqlConnection SourceServer = new SqlConnection(SourceConnectionString);
> SourceServer.Open();
>
> SqlCommand sourcecmd = new SqlCommand();
> sourcecmd.Connection = SourceServer;
> sourcecmd.CommandType = CommandType.StoredProcedure;
> sourcecmd.CommandText = "sp" + TableName + "Select";
> sourcecmd.CommandTimeout = 1800;
> sourcecmd.Parameters.Add("myid", SqlDbType.Int).Value = MyId;
> SqlDataReader read = sourcecmd.ExecuteReader();
>
> It's on that ExecuteReader that this error is happening. Anyone else
> experiencing this? And what have you done to solve this?
>
> -BKN
>

"Bryce K. Nielsen" <(E-Mail Removed)> wrote in
news:#$(E-Mail Removed):

> Suddenly this week, I've started getting this error message:
>
> System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQ
> uery requires an open and available Connection. The connection's
> current state is connecting.
>
> I'm very puzzled since I'm not calling "ExecuteNonQuery" and I
> am calling "Open", which I've always assumed was Synchronous,
> i.e. would not return until either A) the connection was open or
> B) there was an error. Here is effectively what I am doing:
>
> SqlConnection SourceServer = new
> SqlConnection(SourceConnectionString); SourceServer.Open();
>
> SqlCommand sourcecmd = new SqlCommand();
> sourcecmd.Connection = SourceServer;
> sourcecmd.CommandType = CommandType.StoredProcedure;
> sourcecmd.CommandText = "sp" + TableName + "Select";
> sourcecmd.CommandTimeout = 1800;
> sourcecmd.Parameters.Add("myid", SqlDbType.Int).Value = MyId;
> SqlDataReader read = sourcecmd.ExecuteReader();
>
> It's on that ExecuteReader that this error is happening. Anyone
> else experiencing this? And what have you done to solve this?

Bryce,

This sounds like a race condition.

Does your app have multiple threads calling this code?

Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

> This sounds like a race condition.
>
> Does your app have multiple threads calling this code?
>

Yes, each of these threads open a new connection, so you're probably right.
Is there a thread-safe way to create a connection and have it properly pull
from the connection pool?

-BKN

> Also if you check the state of the connection just before the
> ExecuteReader(), what do you get?
>

I'm confused on this though, isn't Connection.Open() synchronous? I mean
won't it not come back until the connection is open? Is there a way to make
it synchronous?

-BKN

"Bryce K. Nielsen" <(E-Mail Removed)> wrote in
news:#(E-Mail Removed):

>> This sounds like a race condition.
>>
>> Does your app have multiple threads calling this code?
>>
>
> Yes, each of these threads open a new connection, so you're
> probably right. Is there a thread-safe way to create a
> connection and have it properly pull from the connection pool?

Bryce,

What might do the trick is a thread-safe factory that ensures only
one thread at a time can construct a connection instance (untested):


public class SqlConnectionFactory
{
private static readonly object _lockObject = new object();

public static SqlConnection GetConnection(string connectionString)
{
lock(_lockObject)
{
return new SqlConnection(connectionString);
}
}
}


Also see Jon Skeet's excellent article on multithreading in .Net for
more info:

http://www.yoda.arachsys.com/csharp/threads/

--
Hope this helps.

Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

> public class SqlConnectionFactory
> {
> private static readonly object _lockObject = new object();
>
> public static SqlConnection GetConnection(string connectionString)
> {
> lock(_lockObject)
> {
> return new SqlConnection(connectionString);
> }
> }
> }
>
>

Help me understand what's going on here. How does this make it thread safe?

I was thinking as a "hack" workaroud to add a Sleep(500) between each Object
Creation call...

-BKN

> public class SqlConnectionFactory
> {
> private static readonly object _lockObject = new object();
>
> public static SqlConnection GetConnection(string connectionString)
> {
> lock(_lockObject)
> {
> return new SqlConnection(connectionString);
> }
> }
> }
>
>

K, I think I understand this now (previously I was unaware of the lock()
clause). So because this is a static class, I don't have to worry about
multiple instances, correct? Also, correct my understanding of lock(),
ThreadA calls GetConnection and has LOCKed the object, when ThreadB calls
GetConnection, does it error because the object is already locked, or does
it wait until the object is unlocked?

Thanks for the help,

-BKN

> K, I think I understand this now (previously I was unaware of the lock()
> clause). So because this is a static class, I don't have to worry about
> multiple instances, correct? Also, correct my understanding of lock(),
> ThreadA calls GetConnection and has LOCKed the object, when ThreadB calls
> GetConnection, does it error because the object is already locked, or does
> it wait until the object is unlocked?
>

Well I tried that and was still getting the error. It really feels like
there's a threaded race issue. So, as a workaround, I tried adding this
code:

while (MyConnection.State != ConnectionState.Open)
System.Threading.Thread.Sleep(500);

But it's still getting that error. I'm at a complete loss as to what's wrong
or what to do now...

-BKN

"Bryce K. Nielsen" <(E-Mail Removed)> wrote:

> > public class SqlConnectionFactory
> > {
> > private static readonly object _lockObject = new object();
> >
> > public static SqlConnection GetConnection(string connectionString)
> > {
> > lock(_lockObject)
> > {
> > return new SqlConnection(connectionString);
> > }
> > }
> > }
> >
> >
>
> Help me understand what's going on here. How does this make it thread safe?
>
> I was thinking as a "hack" workaroud to add a Sleep(500) between each Object
> Creation call...

The code above serializes the construction of SqlConnection(). In my
experience, it hasn't been necessary. I've written many servers which
all use SqlConnection and call Open, concurrently (but always with one
SqlConnection object per thread).

-- Barry

--
http://barrkel.blogspot.com/