ADO Connection still connecting after Open?

[Bryce K. Nielsen]

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

System.Data.SqlClient.SqlConnection(GetOpenConnect ion)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

[Yoganathan Sivaram]

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" <br***@sysonyx.com> wrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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

System.Data.SqlClient.SqlConnection(GetOpenConnect ion)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

[Chris R. Timmons]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote in
news:#$**************@TK2MSFTNGP04.phx.gbl:

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

System.Data.SqlClient.SqlConnection(GetOpenConnect ion)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/

[Bryce K. Nielsen]

> 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

[Bryce K. Nielsen]

> 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

[Chris R. Timmons]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote in
news:#r**************@TK2MSFTNGP02.phx.gbl:

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/

[Bryce K. Nielsen]

> 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

[Bryce K. Nielsen]

> 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

[Bryce K. Nielsen]

> 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

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> 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/

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

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?

lock() acquires the monitor for the object. Every object logically has a
monitor associated with it (it's lazily allocated by the CLR when you
call Monitor.Enter on an object). A monitor is basically just like a
critical section: if the monitor is already acquired by another thread,
the thread blocks until it is released.

lock(obj) is equivalent to:

Monitor.Enter(obj);
try
{
}
finally
{
Monitor.Exit(obj);
}

(There are some other features, with Monitor.Pulse, Monitor.Wait etc.,
which give more of the Java-style monitor-related semantics if you need
them.)

-- Barry

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

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

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...

Are you sharing the connection between multiple threads? Does the
problem also occur with only a single thread?

-- Barry

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

[Bryce K. Nielsen]

> 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).

Well, that's what I'm trying to do (as you replied on my other thread). I
have these 12 threaded processes all creating a new SqlConnection but appear
to be running into a concurrency issue. I tried the above but still have
problems, even after adding that while loop. I'm at a complete loss...

-BKN

[Bryce K. Nielsen]

> Are you sharing the connection between multiple threads? Does the

problem also occur with only a single thread?

No, I have 12 different threads, spawned from delegates (which I'm in the
process of moving away from delegates and using Thread.Start instead) and
the first thing that happens in my spawned method is to create a new
connection object.

The strange thing about all of this is I only recently started getting these
errors. I can only thing of 2 things that have changed: I moved all the
"process" code from the Form onto it's own thread, and I added a lot of
Connection.Close() calls, to make sure previous accesses to the connection
were released back into the connection pool.

What really puzzles me is why this is happening at all. Isn't
Connection.Open synchronous? I mean, when I call Open(), shouldn't my
process "hang" until it's the connection has actually opened?

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

Are you sharing the connection between multiple threads? Does the
problem also occur with only a single thread?

No, I have 12 different threads, spawned from delegates (which I'm in the
process of moving away from delegates and using Thread.Start instead) and
the first thing that happens in my spawned method is to create a new
connection object.

The strange thing about all of this is I only recently started getting these
errors. I can only thing of 2 things that have changed: I moved all the
"process" code from the Form onto it's own thread, and I added a lot of
Connection.Close() calls, to make sure previous accesses to the connection
were released back into the connection pool.

What really puzzles me is why this is happening at all. Isn't
Connection.Open synchronous? I mean, when I call Open(), shouldn't my
process "hang" until it's the connection has actually opened?

Yes, I would expect that, and I've never had any kind of race between
Open() and first use of the connection.

Are you possibly using the connection after you've called Close(),
indirectly through an IDataReader or something? Did you pass
CommandBehavior.CloseConnection to the ExecuteReader method, yet close
the reader after you've closed the connection (possibly causing a
connection that was passed back to the pool, then handed out again, to
be closed early - total guess, I don't know the implementation)?

What's always worked for me is a call stack that ultimately (after
everything has been stripped away) looks somewhat like this:

using (SqlConnection conn = ...)
{
// ...
using (IDataReader reader = cmd.ExecuteReader())
// ...
}

-- Barry

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

[Bryce K. Nielsen]

> What's always worked for me is a call stack that ultimately (after

everything has been stripped away) looks somewhat like this:

using (SqlConnection conn = ...)
{
// ...
using (IDataReader reader = cmd.ExecuteReader())
// ...
}

That's what my code has morphed into as well, just to make sure everything
is chucked before being used again (I hate GC). I basically have this:

using (SqlConnection conn = new SqlConnection(SourceConnectionString))
{
using (SqlDataReader read = conn.ExecuteReader())
{
using (SqlBulkCopy bc = new SqlBulkCopy(DestinationConnectionString))
{
}
}
}

And it's difficult to determine where exactly the errors are coming. I have
form notification events in between each step, and this error sometimes
happens before the reader, after the reader, before the BulkCopy, etc. And
due to that (in effect I have 24 connections going, 12 on the source server,
12 on the destination server) I'm not sure which server even it's happening
on (but I think it's on the "source" server). Other odd thing is I'm not
calling ExecuteNonQuery *anywhere* in my code. There's a couple
ExecuteScalar and ExecuteReader, but no NonQuery.

And what completely irks me is that when I had the code executing on the
Form, I had non of these problems :(

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

And it's difficult to determine where exactly the errors are coming. I have
form notification events in between each step, and this error sometimes
happens before the reader, after the reader, before the BulkCopy, etc. And
due to that (in effect I have 24 connections going, 12 on the source server,
12 on the destination server) I'm not sure which server even it's happening
on (but I think it's on the "source" server). Other odd thing is I'm not
calling ExecuteNonQuery *anywhere* in my code. There's a couple
ExecuteScalar and ExecuteReader, but no NonQuery.

And what completely irks me is that when I had the code executing on the
Form, I had non of these problems :(

What does the full stack trace for the exception look like?

-- Barry

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

[Chris R. Timmons]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote in
news:ee**************@TK2MSFTNGP02.phx.gbl:

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?

Bryce,

The documentation for SqlConnection states that its instance methods
are not thread safe. The lock statement around the constructor
ensures that only one thread at a time can create a new instance
of an SqlConnection. If the lock statement weren't there, then
multiple threads could conceivably try to create several
instances of SqlConnection at the same time, possibly corrupting
the state of the connection pool in the process.

I think you need to take some time and get your hands dirty with
regard to multithreading. The link to Jon Skeet's article I posted
earlier is an excellent introduction to the subject. Here are some
more:

..Net Documentation: Threading
http://msdn.microsoft.com/library/en...nthreading.asp

C# Programmer's Reference: Threading Tutorial
http://msdn.microsoft.com/library/en...ngTutorial.asp

..Net Documentation: Threading Design Guidelines
http://msdn.microsoft.com/library/en...Guidelines.asp

MSDN: Safe Thread Synchronozation
http://msdn.microsoft.com/msdnmag/is...01/NET/TOC.ASP

MSDN: Programming the Thread Pool in the .NET Framework
http://msdn.microsoft.com/library/en...ogthrepool.asp
I see your name over in the Delphi newsgroups, so this link might
be a little more palatable than the .Net/C# ones. It's a
small e-book describing the basics of multithreading in Delphi.
The concepts are exactly the same in both Delphi and C#. If you know
multithreading in one, then you basically know both:

http://codecentral.borland.com/Item.aspx?id=14809

--
Hope this helps.

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

[Barry Kelly]

"Chris R. Timmons" <crtimmons@X_NOSPAM_Xcrtimmonsinc.com> wrote:

The documentation for SqlConnection states that its instance methods
are not thread safe.
Bryce has already indicated that he's using one SqlConnection object per
thread, so it isn't a sharing issue.
The lock statement around the constructor
ensures that only one thread at a time can create a new instance
of an SqlConnection. If the lock statement weren't there, then
multiple threads could conceivably try to create several
instances of SqlConnection at the same time, possibly corrupting
the state of the connection pool in the process.

This isn't necessary. Constructors aren't instance methods, and besides,
the whole allocation process is thread-safe.

Constructing the connection object does next to nothing. The connection
gets allocated when you call Open(). If you had to serialize all calls
to all instance methods for all connections anywhere in the application,
well, that would be a serious scalability problem. It isn't the case.

-- Barry

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

[Bryce K. Nielsen]

Arrgggg, this is killing me. I've tried all sorts of different things and am
STILL getting a problem.

Here's a brief overview of what I'm doing:
- Form creates a MainProcess object that starts a Thread executing a method
on this object.
- This method does a bunch of non database preparation steps
- The "mid" step selects data from 12 tables from a "source" SQLServer and
SqlBulkCopy's them into a "destination" SQLServer
- To speed things up, these 12 transfers are threaded.
- This is how I decided to thread the transfers:
- 12 objects are created, passed in ConnectionStrings and TableNames to
use
- 12 threads are created, executing the object's Process() method
- Inside Process() a new SqlConnection is created using the
SourceConnectionString
- A SqlCommand.ExecuteReader is called to get the data from the table
- A new SqlBulkCopy object is created using the
DestinationConnectionString and executed with the source SqlDataReader.

Here's a listing of the various errors I've been getting:
- ExecuteNonQuery requires an open and available Connection. The
connection's current state is connecting.
- 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.
- A transport-level error has occurred during connection clean-up.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.)
- The SqlDbType enumeration value, 0, is invalid. Parameter name:
SqlDbType

Here's a summary of everything I've tried to fix this:
- Create SQL Connections in the proposed thread-safe manner
- Use a different ConnectionString (so not pulling from connection pool of
other SQL statements being executed)
- Added "Pooling='false';" to ConnectionString to make sure new Connection
is used.
- Added all Connection.Close call to AFTER thread.join() (in case for some
reason the SQLConnection was somehow shared between objects and once one had
completed and closed, the was causing the other to fail)
- Tried putting the SqlConnection inside a USING() clause, forcing a new
connection to be created each time.

Nothing seems to be working for me. And what's really getting my goat is
that all of this was working with ZERO problems when I had the main process
executing on the Form's thread. ALL of the errors make NO sense to me at
all. The only SqlDbType I'm using is SqlDbType.Int (I'm not doing anything
dynamic there). Why would that work great 99% of the time and suddenly stop
with one or two transfers. Same with the TCP connection error. The pooling
error I'm completed baffled (but made me try different things, like forcing
no pooling, etc). And the Connection State error really puzzles me since the
Connection.Open was called, shouldn't it be Open?!?

Any other suggestions?

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

Arrgggg, this is killing me. I've tried all sorts of different things and am
STILL getting a problem.

OK. It looks a tough one to crack.

Does it happen on other machines (i.e. it's not some broken install of
the SQL client)?

Have you tried detaching it from your user interface, so it runs only in
the console?

The reasons I'm suggesting this:

1) Remove the UI interaction / cross-thread communication as a possible
source of error.
2) Reduce complexity for the next phase of analysis.

If the problem still occurs on the console, there are some things we can
look into with the SOS debugger extensions (.load sos in Immediate
window or using WinDbg). In particular, I'd be looking for inappropriate
sharing of objects:

!dumpheap -type Sql will find all objects with Sql in the name of the
type
!gcroot <addr> will find what's pointing to the object and keeping it
alive
!dumpobj <addr> will dump the values of dword fields of an object.

Once an object is found, it's easier to work with if you can find it in
the VS debugger, though.

It still sounds like some kind of cross-thread sharing problem; either
that, or some kind of heap corruption that doesn't cause a CLR crash.
I'm wondering: is it that bulk-copy isn't safe for concurrent
operations? That doesn't sound likely to me.

Another idea, to fix your problem without getting too bogged down:
structure the copying operation as a separate application, and run
multiple copies of that application in parallel. You can use
System.Diagnostics.Process for that, along with Process.WaitForExit(),
using a very similar approach to Thread.Join().

-- Barry

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

[Barry Kelly]

Barry Kelly <ba***********@gmail.com> wrote:

Does it happen on other machines (i.e. it's not some broken install of
the SQL client)?

Though on second thoughts, the .NET SQL Server client is wholly managed,
IIRC; still, it can't hurt to try.

-- Barry

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

[Bryce K. Nielsen]

> Does it happen on other machines (i.e. it's not some broken install of

the SQL client)?

Good question, let me run it on another server to see. That was actually
part of my question, the only other thing that changed recently was a
Windows Update on my dev machine.
Have you tried detaching it from your user interface, so it runs only in
the console?

I'll try that as well. Classic debugging, remove as many variables as
possible to isolate the problems.

-BKN

[Bryce K. Nielsen]

> It still sounds like some kind of cross-thread sharing problem; either

that, or some kind of heap corruption that doesn't cause a CLR crash.
I'm wondering: is it that bulk-copy isn't safe for concurrent
operations? That doesn't sound likely to me.

I should add that when I made SqlConnection thread-safe through the
ConnectionFactory, I decided to do the same thing with the SqlBulkCopy as a
"just in case" measure.

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

I should add that when I made SqlConnection thread-safe through the
ConnectionFactory, I decided to do the same thing with the SqlBulkCopy as a
"just in case" measure.

FWIW, I don't think serializing the construction could possibly help.
I've looked in Reflector, and traced through the construction logic; it
looks like the work was done to make it threadsafe.

I can't imagine it being any other way, frankly; constructing an object
is (somewhat) like calling a static method. The possibility of two
threads executing a method on the same object at the same time is the
primary reason that instance methods unsafe for threading in the absence
of thread safety work; that's impossible with constructors.

The general pattern for static methods and properties in .NET is that
they are thread-safe.

-- Barry

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

[Bryce K. Nielsen]

> FWIW, I don't think serializing the construction could possibly help.

I've looked in Reflector, and traced through the construction logic; it
looks like the work was done to make it threadsafe.

I ran on a different machine, and got one of the three errors each time. I
started researching one of these errors and found this:

Here's another example. Let's say the application loses network connectivity
while its reading results from a SqlDataReader.
In ADO.NET 1.1 this would have resulted in an exception message - "General
network error. Check your network documentation."
This same scenario in ADO.NET 2.0 gives the error message - "A
transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.)".

I'm using a SqlDataReader, and some of the errors seem to happen at either
ExecuteReader or in the SqlBulkCopy (which is probably pumping through the
reader).

This is making me wonder if this is happening:
- I have the 12 threads creating connections
- These 12 connections are pulled from the connection pool
- Somehow, the connection really is shared (due to being pulled from pool)
- One of the 12 finishes the process and closes the connection
- This causes one of the other connections who happened to be sharing this
connection to have strange errors occur.

I don't know if this theory is at all accurate though, since I added
"Pooling='false';" to the connection string, so it shouldn't be pooling?

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

I'm using a SqlDataReader, and some of the errors seem to happen at either
ExecuteReader or in the SqlBulkCopy (which is probably pumping through the
reader).

This is making me wonder if this is happening:
- I have the 12 threads creating connections
- These 12 connections are pulled from the connection pool
- Somehow, the connection really is shared (due to being pulled from pool)
- One of the 12 finishes the process and closes the connection
- This causes one of the other connections who happened to be sharing this
connection to have strange errors occur.

I don't know if this theory is at all accurate though, since I added
"Pooling='false';" to the connection string, so it shouldn't be pooling?

Have you tried using the tracing tools (SQL Profiler, ADO.NET trace)?

This link looks interesting:

http://msdn.microsoft.com/library/de...dataaccess.asp

It's for .NET 2.0 Beta 1, but the code calling Bid is all still there.
There should be more up to date information on Google.

I don't have any more suggestions beyond what I've already suggested.

-- Barry

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

[Bryce K. Nielsen]

I think I finally figured it out. And as is the case with most bugs, it's
happening in a completely different area than I suspected it to happen.
You'd think 10 years of debugging applications would have taught me to look
in the least suspectful areas. Bugs are always in the last place you look...

In my data processing threads, I'm raising events at each step of the way to
alert the GUI of the progress. This does 2 things, it updates a couple
visual controls (a progress bar, label, adds to a memo, etc) AND it does a
simple insert into a log table. Previously, since all the main processing
was done on the Form's thread, I called Form.Invoke(event), so all the
logging was also done on the Form's thread. I have now created a new
"process" thread that does the main processing PLUS all the logging. It
creates the 12 data-processing threads. It has a bunch of events the Form
now subscribes to, and passes-through events from the data-processing
threads. It looks like the errors I'm getting are happening on the Logging
stage.

This answers a lot of questions:
- The simple insert is ExecuteNonQuery (something that was puzzling me for
a long time, since no where else am I executing non query but the error
message says I am).
- Answers why this used to work, since the event to update the GUI and log
the event was the same and was being called on Form.Invoke, and thus always
executing on the same thread. Now on 2 different threads, it's failing.

I am wondering now what would be the best way to fix this. Here's how it's
working:
- Form creates MainProcessing object/thread and subscribes to events
- MainProcessing thread creates 12 sub DataProcessing threads and
subscribes to events
- sub DataProcessing thead raises an event to MainProcessing thread.
- MainProcessing thread logs the event in the database ** this is where
error is occuring ** and then raises same event to Form.
- Form updates GUI controls on it's own thread.

Since the thread is raising event to object, it's executing on that thread
and not on the original object's thread. Is there a way to merge this in the
event? Otherwise what, am I going to need to create/destroy the Connection
every time I log, to guarantee that the connection is opened on the
appropriate thread?

-BKN

[Barry Kelly]

"Bryce K. Nielsen" <br***@sysonyx.com> wrote:

I think I finally figured it out. And as is the case with most bugs, it's
happening in a completely different area than I suspected it to happen.
You'd think 10 years of debugging applications would have taught me to look
in the least suspectful areas. Bugs are always in the last place you look... [...] was done on the Form's thread, I called Form.Invoke(event), so all the
logging was also done on the Form's thread. I have now created a new
I had a deep suspicion that it was your UI interface logic that was
causing the problem :) I didn't want to say it explicitly...

I tried to hint with:

* Are you sharing the connection between multiple threads? Does the
problem also occur with only a single thread?

* What does the full stack trace for the exception look like?

* Have you tried detaching it from your user interface, so it runs only
in the console?

.... slowly getting more explicit :)
This answers a lot of questions:
- The simple insert is ExecuteNonQuery (something that was puzzling me for
a long time, since no where else am I executing non query but the error
message says I am).
The stack trace should have told you this straight away
(Exception.StackTrace).
I am wondering now what would be the best way to fix this. Here's how it's
working:
- Form creates MainProcessing object/thread and subscribes to events
- MainProcessing thread creates 12 sub DataProcessing threads and
subscribes to events
- sub DataProcessing thead raises an event to MainProcessing thread.
- MainProcessing thread logs the event in the database ** this is where
error is occuring ** and then raises same event to Form.
- Form updates GUI controls on it's own thread.

Since the thread is raising event to object, it's executing on that thread
and not on the original object's thread. Is there a way to merge this in the
event?
Can you rephrase these two sentences? Merge what into what, exactly?
Otherwise what, am I going to need to create/destroy the Connection
every time I log, to guarantee that the connection is opened on the
appropriate thread?

ISTM that you need a separate connection if you want to put the logging
in the MainProcessing thread. Wouldn't it be better to do you logging on
the thread that does the work, where it's part of the same transaction
etc.? (I don't know how you're handling transactions etc.)

This blog (see the comments) has good information on the transaction
issues for cross-thread transactions, to make all the connections use
the same transaction:

http://blogs.msdn.com/angelsb/archiv...07/175586.aspx

-- Barry

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

[Bryce K. Nielsen]

> * Have you tried detaching it from your user interface, so it runs only

in the console?

Unfortunately, the problem would have still been there. The problem wasn't
with the GUI, but rather with the DB-logging.

Since the thread is raising event to object, it's executing on that
thread
and not on the original object's thread. Is there a way to merge this in
the
event?

Can you rephrase these two sentences? Merge what into what, exactly?

Here's how the threads are working:

- FormThread launches a MainProcessThread
- MainProcessThread does a bunch of junk (including DB logging)
- MainProcessThread launches 12 DataProcessThreads
- DataProcessThreads SqlBulk copy junk
- DataProcessThreads raise events that are DB-Logged in the
MainProcessThread.

Originally my SqlConnection object for my DBLogging was only created once,
but open/closed on each log. Originally this all happened on the FormThread,
and whenever the DataProcessThreads raised an event, I called
Form.Invoke(event) to log it. So, the connection was created on the form
thread, then open/closed still on the form thread.

Now, separating the main processing from the Form thread, the DBLogging is
in this thread too. Problem is, events raised from the DataProcess thread
are executing on the DataProcess thread, not the MainProcessThread. Since my
object is not a Control, I don't have any way of invoking this event on the
MainProcess thread (where the Connection object was created).

So here's my current delima. I have an object on ThreadA that subscribes to
an event of an object on ThreadB. When ThreadB raises the event, how do I
get the code to execute on ThreadA?

My current workaround is to just create the SqlConnection object each time I
log a message. Seems a little expensive, but at least it's working now.

-BKN