How to make SQL Server close session when connection is lost?

[Guest]

I have an Winforms application that's using ADO.NET to access a SQL Server database. The connection is NOT pooled, and is kept alive for as long as the application is active. It also creates a ##TEMP table to be able to detect an already running instance (only one can run at a time).

If the TCP connection between the application and SQL Server is broken, the connection stays alive "forever" in SQL Server, and the ##TEMP table is never removed, locking out other instances.
Is there something that the application can specify on the connection so that SQL Server can be made aware when the connection has been broken, and automatically close the session? I'd rather not have to require using the KILL command to kill the orphaned connection.

 

[William Ryan eMVP]

Per:

There are other ways to accomplish each of these goals without the
mechanisms that you have in play. So before I continue, are you 100% sold
on this methodology or are you open to some other mechanisms to get you to
the same place. I'm sure you have some good reasons for going about
everythign this way, but there certainly are some more straightforward ways
to accomplish the same things - that will probably be a lot more scalable
and flexible?

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

I have an Winforms application that's using ADO.NET to access a SQL Server

database. The connection is NOT pooled, and is kept alive for as long as the
application is active. It also creates a ##TEMP table to be able to detect
an already running instance (only one can run at a time).

If the TCP connection between the application and SQL Server is broken,

the connection stays alive "forever" in SQL Server, and the ##TEMP table is
never removed, locking out other instances.

Is there something that the application can specify on the connection so

that SQL Server can be made aware when the connection has been broken, and
automatically close the session? I'd rather not have to require using the
KILL command to kill the orphaned connection.

 

[William \(Bill\) Vaughn]

Until we get ADO .NET 2.0, we won't have an event to signal that the
connection has been dropped. But I'm with Mr. Ryan. There are lots of better
approaches to use.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

I have an Winforms application that's using ADO.NET to access a SQL Server

database. The connection is NOT pooled, and is kept alive for as long as the
application is active. It also creates a ##TEMP table to be able to detect
an already running instance (only one can run at a time).

If the TCP connection between the application and SQL Server is broken,

the connection stays alive "forever" in SQL Server, and the ##TEMP table is
never removed, locking out other instances.

Is there something that the application can specify on the connection so

that SQL Server can be made aware when the connection has been broken, and
automatically close the session? I'd rather not have to require using the
KILL command to kill the orphaned connection.

 

[Angel Saenz-Badillos[MS]]

Per Hellberg,

Can you elaborate on how the TCP Connection is broken? I would have thought
that in this scenario Sql Server would detect that the connection is broken
and clean up. I would like to set up a repro for this scenario.

Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

I have an Winforms application that's using ADO.NET to access a SQL Server

database. The connection is NOT pooled, and is kept alive for as long as the
application is active. It also creates a ##TEMP table to be able to detect
an already running instance (only one can run at a time).

If the TCP connection between the application and SQL Server is broken,

the connection stays alive "forever" in SQL Server, and the ##TEMP table is
never removed, locking out other instances.

Is there something that the application can specify on the connection so

that SQL Server can be made aware when the connection has been broken, and
automatically close the session? I'd rather not have to require using the
KILL command to kill the orphaned connection.