Connection closed durring application execution

I'm using Sql Server 2000 in my .NET application.
Once the application created a connection, the connection seams to stay
opened even after the server was stopped.
Why is it happening, and it there a way for my app to get events when the
server is off?Amir Shitrit wrote:
> I'm using Sql Server 2000 in my .NET application.
> Once the application created a connection, the connection seams to
> stay opened even after the server was stopped.
> Why is it happening, and it there a way for my app to get events when
> the server is off?
SQL Server is not going to notify all connected clients when it shuts down.
It's really up to your application to make sure you have the necessary error
handling in place. You could try checking the SqlConnection.State property
to see if that is closed, but I'm not convinced the connection is going to
know that SQL Server shut down.
Can you explain the problem you are running into? I'm guessing possibly what
you want is a way to proactively check whether the connection is open before
executing any SQL from the client. You would normally use the State property
for this and check to see that it's open. If you're running into another
issue, you might posting this question to the .Net newsgroups for
programmatic help.
http://msdn.microsoft.com/library/d...r />
etopic.asp
David Gugick
Quest Software|||This behavior is expected. AFAIK, there isn't a way to detect a broken
connection without trying to use it. You can catch the error in .NET and
attempt recovery, if desired.
You might consider using the 'open late, close earlier' development pattern
rather than keeping connections open for extended periods. With connection
pooling, this mitigates the affect of broken connections on your
application.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:607AC0C8-94A0-47A8-BA2B-B451C80A85D7@.microsoft.com...
> I'm using Sql Server 2000 in my .NET application.
> Once the application created a connection, the connection seams to stay
> opened even after the server was stopped.
> Why is it happening, and it there a way for my app to get events when the
> server is off?|||As you stated, I can't use the SqlConnection.State property since it won't
reflect the real state of the connection, nor do I get an error when trying
to execute a SELECT statement, but rather an empty DataSet (the Fill method
returns with 0 rows affected - which means nothing to me).
"Dan Guzman" wrote:

> This behavior is expected. AFAIK, there isn't a way to detect a broken
> connection without trying to use it. You can catch the error in .NET and
> attempt recovery, if desired.
> You might consider using the 'open late, close earlier' development patter
n
> rather than keeping connections open for extended periods. With connectio
n
> pooling, this mitigates the affect of broken connections on your
> application.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:607AC0C8-94A0-47A8-BA2B-B451C80A85D7@.microsoft.com...
>
>|||> nor do I get an error when trying
> to execute a SELECT statement, but rather an empty DataSet (the Fill
> method
> returns with 0 rows affected - which means nothing to me).
So you are saying you don't get an exception when you execute a
SqlDataAdapter.Fill method after the open connection was terminated? That
doesn't right. What version of VS are you using? I tested the following
code snippet with VS 2003 and VS 2005 and it throws as expected. Can you
post code that reproduces your issue?
try
{
string connectionString = string.Format(
"Persist Security Info=false;Initial Catalog={0};Data
Source={1};Integrated Security=SSPI",
new string[] { "tempdb", "." });
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
MessageBox.Show("Restart SQL Server now");
SqlCommand command = new SqlCommand("SELECT 1 AS TestCol", connection);
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
//this should throw
da.Fill(ds);
MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " rows returned");
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:63B68856-D685-46D4-B66B-3125DC41F6A6@.microsoft.com...
> As you stated, I can't use the SqlConnection.State property since it won't
> reflect the real state of the connection, nor do I get an error when
> trying
> to execute a SELECT statement, but rather an empty DataSet (the Fill
> method
> returns with 0 rows affected - which means nothing to me).
> "Dan Guzman" wrote:
>|||Let me put things in place.
Step 1: I opened the connection to the server while it was running.
Step 2: I closed the connection.
Step 3: I stopped the server.
Step 4: I reopened the connection while the server is stopped (no exception
so far).
Step 5: I called the SqlDataAdapter.Fill method (no exception) and returned
0 rows.
Does it make sence?
"Dan Guzman" wrote:

> So you are saying you don't get an exception when you execute a
> SqlDataAdapter.Fill method after the open connection was terminated? That
> doesn't right. What version of VS are you using? I tested the following
> code snippet with VS 2003 and VS 2005 and it throws as expected. Can you
> post code that reproduces your issue?
> try
> {
> string connectionString = string.Format(
> "Persist Security Info=false;Initial Catalog={0};Data
> Source={1};Integrated Security=SSPI",
> new string[] { "tempdb", "." });
> SqlConnection connection = new SqlConnection(connectionString);
> connection.Open();
> MessageBox.Show("Restart SQL Server now");
> SqlCommand command = new SqlCommand("SELECT 1 AS TestCol", connection)
;
> SqlDataAdapter da = new SqlDataAdapter(command);
> DataSet ds = new DataSet();
> //this should throw
> da.Fill(ds);
> MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " rows returned")
;
> connection.Close();
> }
> catch (Exception ex)
> {
> MessageBox.Show(ex.ToString());
> }
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:63B68856-D685-46D4-B66B-3125DC41F6A6@.microsoft.com...
>
>|||> Let me put things in place.
> Step 1: I opened the connection to the server while it was running.
> Step 2: I closed the connection.
> Step 3: I stopped the server.
> Step 4: I reopened the connection while the server is stopped (no
> exception
> so far).
> Step 5: I called the SqlDataAdapter.Fill method (no exception) and
> returned
> 0 rows.
> Does it make sence?
>
Step 4 should throw an exception. I've tried to recreate your problem
without success. Please run the code below in your environment to see if it
reproduces the problem. If not, please post a test code snippet that does
reproduce the problem according to your narrative.
try
{
string connectionString = string.Format(
"Persist Security Info=false;Initial Catalog={0};Data
Source={1};Integrated Security=SSPI",
new string[] { "tempdb", "." });
SqlConnection connection = new SqlConnection(connectionString);
//Step 1: I opened the connection to the server while it was running.
connection.Open();
//Step 2: I closed the connection.
connection.Close();
//Step 3: I stopped the server.
MessageBox.Show("Stop SQL Server now");
//Step 4: I reopened the connection while the server is stopped
//this should throw with SQL Server stopped
connection.Open();
SqlCommand command = new SqlCommand("SELECT 1 AS TestCol", connection);
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
//Step 5: I called the SqlDataAdapter.Fill method
da.Fill(ds);
MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " rows returned");
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:73C8E728-14DD-4F50-B0E8-C6A9B788C396@.microsoft.com...
> Let me put things in place.
> Step 1: I opened the connection to the server while it was running.
> Step 2: I closed the connection.
> Step 3: I stopped the server.
> Step 4: I reopened the connection while the server is stopped (no
> exception
> so far).
> Step 5: I called the SqlDataAdapter.Fill method (no exception) and
> returned
> 0 rows.
> Does it make sence?
>|||I run the code you sent, bu I didn't get an exception in step 4 but rather i
n
step 5 when calling the Fill method.
Moreover, I can't reproduce the my original problem, and it's a bit
difficult for me to send the code to the forum, but I will try to describe
the general idea:
1. I opened the connection and feteched data from a table in order to
display it in a DataGrid (the grid was full with more than 3000 rows).
When I finished I closed the connection.
2. Once in 10 minutes I reopen the connection and refill the grid with data
from the same table (3000 rows).
I wanted to see what happend if the I stopped the server between those 10
minutes, and instead of getting an exception, the DataGrid became empty.
"Dan Guzman" wrote:

> Step 4 should throw an exception. I've tried to recreate your problem
> without success. Please run the code below in your environment to see if
it
> reproduces the problem. If not, please post a test code snippet that does
> reproduce the problem according to your narrative.
> try
> {
> string connectionString = string.Format(
> "Persist Security Info=false;Initial Catalog={0};Data
> Source={1};Integrated Security=SSPI",
> new string[] { "tempdb", "." });
> SqlConnection connection = new SqlConnection(connectionString);
> //Step 1: I opened the connection to the server while it was running.
> connection.Open();
> //Step 2: I closed the connection.
> connection.Close();
> //Step 3: I stopped the server.
> MessageBox.Show("Stop SQL Server now");
> //Step 4: I reopened the connection while the server is stopped
> //this should throw with SQL Server stopped
> connection.Open();
> SqlCommand command = new SqlCommand("SELECT 1 AS TestCol", connection)
;
> SqlDataAdapter da = new SqlDataAdapter(command);
> DataSet ds = new DataSet();
>
> //Step 5: I called the SqlDataAdapter.Fill method
> da.Fill(ds);
> MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " rows returned")
;
> connection.Close();
> }
> catch (Exception ex)
> {
> MessageBox.Show(ex.ToString());
> }
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:73C8E728-14DD-4F50-B0E8-C6A9B788C396@.microsoft.com...
>
>|||>I run the code you sent, bu I didn't get an exception in step 4 but rather
>in
> step 5 when calling the Fill method.
This depends how long you waited before continuing. I probably waiting
long enough so that the closed connection was released from the pool,
causing the Open to fail.

> Moreover, I can't reproduce the my original problem, and it's a bit
> difficult for me to send the code to the forum, but I will try to describe
> the general idea:
I understand that it may be difficult to send your actual code but it should
be possible to create a simple test application that reproduces the problem
in your environment. That's what I've been trying to do here. It's very
difficult to help you unless the problem can be repeated.
Below is some test code (DataGrid declared and instanciated separately) that
should recreate the problem according to your specs. However, I could not
reproduce your problem on my system.
try
{
SqlCommand command;
SqlDataAdapter da;
DataSet ds;
string connectionString = string.Format(
"Persist Security Info=false;Initial Catalog={0};Data
Source={1};Integrated Security=SSPI",
new string[] { "tempdb", "." });
SqlConnection connection = new SqlConnection(connectionString);
//1. I opened the connection and feteched data from a table in order to
//display it in a DataGrid (the grid was full with more than 3000 rows).
//When I finished I closed the connection.
connection.Open();
command = new SqlCommand("SELECT TOP 3000 * FROM master..syscolumns",
connection);
da = new SqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);
this.dataGrid1.DataSource = ds;
connection.Close();
//2. Once in 10 minutes I reopen the connection and refill the grid with
data
//from the same table (3000 rows).
//I wanted to see what happend if the I stopped the server between those
10
//minutes, and instead of getting an exception, the DataGrid became
empty.
MessageBox.Show("Stop SQL Server and wait 10 minutes");
connection.Open();
command = new SqlCommand("SELECT TOP 3000 * FROM master..syscolumns",
connection);
da = new SqlDataAdapter(command);
ds = new DataSet();
da.Fill(ds);
this.dataGrid1.DataSource = ds;
connection.Close();
MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " rows returned");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:73C926B0-8247-441B-88E9-4D8D56E46BC4@.microsoft.com...
>I run the code you sent, bu I didn't get an exception in step 4 but rather
>in
> step 5 when calling the Fill method.
> Moreover, I can't reproduce the my original problem, and it's a bit
> difficult for me to send the code to the forum, but I will try to describe
> the general idea:
> 1. I opened the connection and feteched data from a table in order to
> display it in a DataGrid (the grid was full with more than 3000 rows).
> When I finished I closed the connection.
> 2. Once in 10 minutes I reopen the connection and refill the grid with
> data
> from the same table (3000 rows).
> I wanted to see what happend if the I stopped the server between those 10
> minutes, and instead of getting an exception, the DataGrid became empty.
> "Dan Guzman" wrote:
>|||I tried to reproduce my code in a simpler way, but it seems that you were
right.
I it doesn't happen again, but rather throws an exception in the Open method
as you expected.
(I used a Timer that opend the connection, filled the data and closed the
connection once in 10 minutes).
Conclusion: I have absolutely no idea was exactly happend in the original
scenario, but if it will happen to me again, I'll let the forum know.
Thank you very much for your help.
"Amir Shitrit" wrote:

> I'm using Sql Server 2000 in my .NET application.
> Once the application created a connection, the connection seams to stay
> opened even after the server was stopped.
> Why is it happening, and it there a way for my app to get events when the
> server is off?