Been struggling with this one for a while. It seems to be a popular topic of discussion in several forums, however, all suggested fixes have failed to work for me. I’ve tried setting POOLING=FALSE. I’ve also tried setting CONNECTION RESET=FALSE. Although both of these have allowed me to keep a connection open for slightly longer, after a while I would get the familiar “General network error. Check your network documentation.” error message. It seems that, in general, SQL 7.0 / ADO .NET does not like it when you keep connections open for more than a half hour, even if you are constantly using that connection. The only way that I’ve been able to deal with the situation is to occasionally close the connection and open it again. Kind of kludgey, but it’s the only fix that has worked. Does anybody else have a suggestion?
I probably won’t be too much help, but I was confused by your statement of leaving the connection open for more than half an hour. In your code you should do clean up, i.e., if you opening a connection to a db with a dataReader object, then you should follow this pretty much to the “T”.
Dim objConn as New OleDbConnection("Some connection string here")
Dim objCmd as OleDbCommand
Dim objDR as OleDbDataReader
objCmd=New OleDbCommand("SELECT * FROM Table_Name", objConn)
objConn.Open()
objDR=objCmd.ExecuteReader()
dgDataGrid.DataSource=objDR
dgDataGrid.DataBind()
objDR.Close() 'close the datareader object
objConn.Close() 'close the connection
This way, as soon as the page queries the db for the required data, it closes the DataReader object and Connection, therefore, elminiating the worry about having the connection open for any amount of time which leads to db server strain. This probably has led to more confusion, hopefully not.
Should the SqlConnection class disconnect on disposal when garbage collected?
You’re right of course, you should formally disconnect, but I’m sure when the method is completed, your SqlConnection should be GCed and disconnected…
Well, the reason that I leave the connection open for over a half hour is because, during that time, I’m using that connection. I suppose I could open and close the connection before and after every SQL query, but it seems like that would be terribly inefficient.
First, read through this thread:
Close your connections when you’re done with them.
Second, we used to experience this error back in the 1.0 days, and you know what fixed it? Restarting IIS. I’ve seen a few different causes to this problem, and one of them seems to be that dreaded MS luck that just says “restart”. You might try that.
Very interesting. As it turns out, I am using v1.0 of the .NET framework. Just out of curiousity, did you stop experiencing this problem when you switched to v1.1?
Also, an interesting sidenote - to get around this bug, I’ve started using CONNECT TIMEOUT = 90; in all of my connection strings. This usually solves the problem. Now, strangely enough, the only time that I’m consistantly butting up against this bug is when I’m attempting to debug a webapp in VS .NET (I’m using VS .NET 2002). Wierd, eh?
always use either ‘using’, or a try / catch / finally block