|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Connection closed durring application executionI'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 sh*trit wrote:
> I'm using Sql Server 2000 in my .NET application. SQL Server is not going to notify all connected clients when it shuts down. > 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? 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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassstatetopic.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. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Amir sh*trit" <AmirShit***@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). Show quote "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 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 sh*trit" <AmirShit***@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? > > > > nor do I get an error when trying So you are saying you don't get an exception when you execute a > to execute a SELECT statement, but rather an empty DataSet (the Fill > method > returns with 0 rows affected - which means nothing to me). 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()); } -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Amir sh*trit" <AmirShit***@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: > >> 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 sh*trit" <AmirShit***@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? >> >> >> 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? Show quote "Dan Guzman" wrote: > > 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 sh*trit" <AmirShit***@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: > > > >> 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 sh*trit" <AmirShit***@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? > >> > >> > >> > > > > Let me put things in place. Step 4 should throw an exception. I've tried to recreate your problem > 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? > 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()); } -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Amir sh*trit" <AmirShit***@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 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. Show quote "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? > > > > 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 sh*trit" <AmirShit***@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 This depends how long you waited before continuing. I probably waiting >in > step 5 when calling the Fill method. 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 I understand that it may be difficult to send your actual code but it should > difficult for me to send the code to the forum, but I will try to describe > the general idea: 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()); } -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Amir sh*trit" <AmirShit***@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: > >> > 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 sh*trit" <AmirShit***@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 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. Show quote "Amir sh*trit" 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? > Conclusion: I have absolutely no idea was exactly happend in the original I'm glad I could assist, although I wish we could have gotten to the bottom > scenario, but if it will happen to me again, I'll let the forum know. > Thank you very much for your help. of this. -- Show quoteDan Guzman SQL Server MVP "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message news:EC88112C-4BCD-4B8B-9EE7-1FF01C89AEE0@microsoft.com... >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 sh*trit" 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?  |
|||||||||||||||||||||||
Other interesting topics