Hi

I have this weird situation happening only in a few client machines:

1/ ADO.NET client application opens a connection to an SQL Server DB.
And you can actually see the connection is open in the SQL Profiler

2/ Wait for a couple of minutes...

3/ Press the button (or whatever) that will trigger an SQL command to be
executed.

4/ Although the application thinks that the connection is still open,
the command times out and the connection state is switched to closed
after the failure.
The command doesn't even show up on SQL Profiler, which means it doesn't
actually hit the server.

The problem doesn't happen if we skip step 2 (i.e. no time gap between
opening the connection and executing the command) and in most client
machines it doesn't happen at all, regardless of the connection idle period.


Thanks,
Lee

Don't know why it happens, but it's not a good idea to have an open
connection just sitting there indefinitely. Connection pooling is quite
efficient. Open and close connections when you need to - don't keep an open
one hanging out there.

"Lee Rennie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I have this weird situation happening only in a few client machines:
>
> 1/ ADO.NET client application opens a connection to an SQL Server DB.
> And you can actually see the connection is open in the SQL Profiler
>
> 2/ Wait for a couple of minutes...
>
> 3/ Press the button (or whatever) that will trigger an SQL command to be
> executed.
>
> 4/ Although the application thinks that the connection is still open,
> the command times out and the connection state is switched to closed
> after the failure.
> The command doesn't even show up on SQL Profiler, which means it doesn't
> actually hit the server.
>
> The problem doesn't happen if we skip step 2 (i.e. no time gap between
> opening the connection and executing the command) and in most client
> machines it doesn't happen at all, regardless of the connection idle
> period.
>
>
> Thanks,
> Lee
>
>

It's perfectly okay to design applications that maintain a connection and
manage server state. We've been doing it for decades. The problem of the
connection timing out is a problem that needs to be addressed. I've built
applications that have held connections open for weeks or longer. We have
seen this when the network hardware is flaky or interrupts the connection
while a hand-shake is going on. I would certainly add exception handlers to
your code to attempt to recover from this--there are plenty of "normal"
things that happen in the course of the day that can break a connection.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Lee Rennie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I have this weird situation happening only in a few client machines:
>
> 1/ ADO.NET client application opens a connection to an SQL Server DB.
> And you can actually see the connection is open in the SQL Profiler
>
> 2/ Wait for a couple of minutes...
>
> 3/ Press the button (or whatever) that will trigger an SQL command to be
> executed.
>
> 4/ Although the application thinks that the connection is still open,
> the command times out and the connection state is switched to closed
> after the failure.
> The command doesn't even show up on SQL Profiler, which means it doesn't
> actually hit the server.
>
> The problem doesn't happen if we skip step 2 (i.e. no time gap between
> opening the connection and executing the command) and in most client
> machines it doesn't happen at all, regardless of the connection idle
> period.
>
>
> Thanks,
> Lee
>
>

Thanks, William. I was actually wondering why it would happen only in a few
client machines, and I'm pretty happy with your explanation and the solution
for it.

And Marina, thanks all the same for your connection pool suggestion, but I
don't think it's the better option in our case. We have a 2-tier
(client-server) single-threaded application, ie. no application/web server
involved. There's absolutely no point on having a connection pool on each
client machine. It's like your pooling with yourself???? And opening a new
connection every time you need considerably hinders the overall application
performance (we've tested that).




"William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It's perfectly okay to design applications that maintain a connection and
> manage server state. We've been doing it for decades. The problem of the
> connection timing out is a problem that needs to be addressed. I've built
> applications that have held connections open for weeks or longer. We have
> seen this when the network hardware is flaky or interrupts the connection
> while a hand-shake is going on. I would certainly add exception handlers
> to your code to attempt to recover from this--there are plenty of "normal"
> things that happen in the course of the day that can break a connection.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
>
> "Lee Rennie" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi
>>
>> I have this weird situation happening only in a few client machines:
>>
>> 1/ ADO.NET client application opens a connection to an SQL Server DB.
>> And you can actually see the connection is open in the SQL Profiler
>>
>> 2/ Wait for a couple of minutes...
>>
>> 3/ Press the button (or whatever) that will trigger an SQL command to be
>> executed.
>>
>> 4/ Although the application thinks that the connection is still open,
>> the command times out and the connection state is switched to closed
>> after the failure.
>> The command doesn't even show up on SQL Profiler, which means it
>> doesn't actually hit the server.
>>
>> The problem doesn't happen if we skip step 2 (i.e. no time gap between
>> opening the connection and executing the command) and in most client
>> machines it doesn't happen at all, regardless of the connection idle
>> period.
>>
>>
>> Thanks,
>> Lee
>>
>>
>
>

I just tripped on something in my ISA server error log that said something
to the effect that the connection on a given IP address timed out. The IP
address in question was one of my local system (inside the domain and the
firewall). I'm going to ask my IT person what would cause that. You might
do the same...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Lee Rennie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks, William. I was actually wondering why it would happen only in a
> few client machines, and I'm pretty happy with your explanation and the
> solution for it.
>
> And Marina, thanks all the same for your connection pool suggestion, but I
> don't think it's the better option in our case. We have a 2-tier
> (client-server) single-threaded application, ie. no application/web server
> involved. There's absolutely no point on having a connection pool on each
> client machine. It's like your pooling with yourself???? And opening a new
> connection every time you need considerably hinders the overall
> application performance (we've tested that).
>
>
>
>
> "William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> It's perfectly okay to design applications that maintain a connection and
>> manage server state. We've been doing it for decades. The problem of the
>> connection timing out is a problem that needs to be addressed. I've built
>> applications that have held connections open for weeks or longer. We have
>> seen this when the network hardware is flaky or interrupts the connection
>> while a hand-shake is going on. I would certainly add exception handlers
>> to your code to attempt to recover from this--there are plenty of
>> "normal" things that happen in the course of the day that can break a
>> connection.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>> "Lee Rennie" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi
>>>
>>> I have this weird situation happening only in a few client machines:
>>>
>>> 1/ ADO.NET client application opens a connection to an SQL Server DB.
>>> And you can actually see the connection is open in the SQL Profiler
>>>
>>> 2/ Wait for a couple of minutes...
>>>
>>> 3/ Press the button (or whatever) that will trigger an SQL command to be
>>> executed.
>>>
>>> 4/ Although the application thinks that the connection is still open,
>>> the command times out and the connection state is switched to closed
>>> after the failure.
>>> The command doesn't even show up on SQL Profiler, which means it
>>> doesn't actually hit the server.
>>>
>>> The problem doesn't happen if we skip step 2 (i.e. no time gap between
>>> opening the connection and executing the command) and in most client
>>> machines it doesn't happen at all, regardless of the connection idle
>>> period.
>>>
>>>
>>> Thanks,
>>> Lee
>>>
>>>
>>
>>
>
>