Error "ExecuteScalar requires an open and available connection..."

Error "ExecuteScalar requires an open and available connection..."

• Marlin7

  Thanks, Paul!

  Could you tell me what you mean by connection pooling or point me to a source where I can find the information.

  The application is a desktop application. It opens a connection when a function starts a new thread and it takes weeks for the thread to finish its work. During this period of time, it accesses the database probably dozens of times every second. It usually works fine for many hours, sometimes for days, without any problem. The application is the only one that accesses the database. No other applications do.

  hz

  
  
  

• TEJKIRAN

  Thanks again, Paul!

  Since my application needs to access the database dozens of times per second, I believe persistent connection makes more sense than opening and closing the connection dozens of times per second.

  As always, I use try and catch extensively and this is why I can pretty much pinpoint to the statements that generate the error.  The error messages are captured by my catch blocks.

  I do use DataTable to get all the data and process them for some functions, however SQL server data tables offer more flexiblity and I thought SQL server always tries to optimize query processing by statistical analysis.  I guess SQL buffers the data my application accesses in memory most of the time.  However I have not done a fair comparison between these two methods for the fuction I am talking about.

  It happened again this afternoon and I checked the System event log and see the same events as I reported in my previous message.

  I am really puzzled by the loss of the local connection that should have nothing to do with the network.

  hz

  
  
  

• gauls

  In general, the strategy that Paul suggested is the best approach. Since we don't really know your code, it's hard to say exactly what is best in your situation, but in my opinion, holding a persistent connection open for long periods of time makes the application more susceptible to these kind of connection errors. The overhead involved in connections going in and out of the pool is minimal, but of course it is more than not doing that at all, and just keeping the connection around. However, using a pooled approach does have some benefits, even in the scenario you describe. For one thing, if you do have an idle period where connections aren't continuously being used, the pool may discard the idle connections in that time. This doesn't happen continuously, but maybe ever 5 minutes or so. Yes, this means that the next time a connection is needed, a new one will have to be made rather than using an existing connection, but it also means that you periodically can refresh your connections so that you don't try to have one connection open for an extremely long period of time.

  I also want to address the question you have about local vs remote connections. In this case, even if your SQL Server is on the same machine as your client, you are probably getting a TCP/IP connection. From what I can tell, you are using OleDbConnection, probably with the SQLOLEDB OLE DB provider. You also mentioned that you are connecting to SQL Server 2005. In that case, if you are specifying the server name as "." or "(local)", it is likely that you are getting the TCP/IP connection. See http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx for more information on this. That blog is geared towards consistent connectivity failures, but it also discusses when various protocols are used with different providers. Based on the error message you are getting, I believe that you have a TCP/IP connection, for whatever reason. That could be why these connections are affected by the errors you are seeing in your system event log. I don't know for sure if those errors are related to your connectivity error, but if they always occur together, I'd say it's a good bet. As for what is causing the system errors in the first place, I don't know.

  This still comes back to the original suggestion, which is to use pooling to mitigate the risks of having a connection open for a long period of time. I will point out that even with pooling, it's possible to have one connection open for a long period of time, especially if your application is constantly opening and closing connections. In that scenario, with the default settings, your connections may never sit idle in the pool long enough to be flushed. There is a way around this using the Connection Lifetime setting, which is checked whenever a connection is put back into the pool. If the lifetime is exceeded at that point, the connection is discarded rather than going into the pool. You may not need to use that though. In your original post, you mentioned that the error generally happens when the app is paused for a period of time. If that is the case, then that period of time should allow the pool to flush, so that you will get new connections when it's unpaused again.

  Finally, there are a couple things to consider, which may or may not be feasible in your situation. The first is that if you are connecting to SQL Server from ADO.NET, you should be using SqlClient instead of OleDb. However, as far as this particular error goes, SqlClient will still have the same potential issues with connections that are persistent for long periods of time. If you are using .NET 2.0 and SqlClient, you will have an advantage in that you can connect over SQL Server 2005's shared memory protocol instead of TCP/IP. Along those same lines, if you need to stick with OleDb, an option is to try the SQLNCLI provider (see the blog again for details). This provider uses an updates network protocol layer, and should also allow you to connect over shared memory. Both of these solutions would avoid the TCP/IP connection altogether. My description here over simplifies when you get a TCP/IP connection and when you can use other protocols, but I just wanted to try to explain more about why the error seems to refer to a remote connection even though your server is local.

  Thanks,
  Sarah

  
  
  

• Mel V

  
  If you're operating locally why not use the shared memory protocol Just enable it (if not already enabled) and set the priority to 1.
  
  

• CharlieRussell

  The connection object does infact have a timeout property...

  Public Overrides ReadOnly Property ConnectionTimeout() As Integer

  Member of: System.Data.OleDb.OleDbConnection

  Summary:

  Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

  Return Values:

  The time in seconds to wait for a connection to open. The default value is 15 seconds.

  Exceptions:

  System.ArgumentException: The value set is less than 0.

  
  
  

• Wolfgang12345

  Thanks a lot for the detailed explanation, Sarah! The information is very educational to me.

  Firstly, let me report that my application has been running without any problem for about 35 hours since I followed the instructions of the following article:

  http://support.microsoft.com/default.aspx scid=kb;en-us;899599

  I did this out of desparation yesterday morning because the problem was occurring reliably within 10 hours of starting the program and I had to revise the program a little bit every time to accomodate the fact the processing was only partly done. The article is for Windows 2003 Server, but my machine is running XP Pro. However I am gettting the same error as described in the article:

  "[DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.".

  I am also running SQL server as the article addresses.

  I will report again if that error does not occur again in a few days. If so, I am sure that fix works. BTW, the problem is NOT machine dependent. I had exactly the same problem with a quite different machine in terms of hardware, but with similar software configuration.

  I enabled both TCP/IP and named pipes for connections to the local server. According to Sarah's message, it seems that TCP/IP connection is probably the culprit of the problem. Yes, the error always occurs together with the TCP/IP events as described in my previous message.

  If the error occurs again, I will configure the SQL server to using named pipes only. If it still does not work, I will try to use SqlConnection to replace OleDbConnection. The reason that I prefer OleDB to SqlClient is that I want to keep my code easily portable to other database servers.

  hz
  

  
  
  

• motioneye

  
  Connection pooling is automatic. It is established for your app when you open and close (release) a connection. Connections released to the pool remain there to be reused or until they expire after a certain period of time and are destroyed.

  It sounds to me like you have a persistent connection and at some point you're losing connectivity to the database. If this connection must remain open during processing for extended periods of time you may have to trap the error (using a Try...Catch block) and open a new connection and DataReader to restart processing.

  Otherwise, if your app doesn't require a persistent connection, close it and then re-open it when processing of the data (access to the database) must continue.

  BTW, you may also want to consider using a DataTable to process chunks of data. Under this scenario a connection is only required during the query/fill process and not when reading the data. It may slow you down a bit, but it's probably more reliable.

  
  
  

• Matt Lin

  Thank you all for the information!

  Firstly, let me report that my program is still running fine 60 hours after it started, so I am more confident that the fix by changing the registry works.

  As for protocols, all but VIA are enabled on that machine. Paul, could you tell me how to set the priority. I cannot figure out. Shared Memory is at the top of the list. Does this mean it has the highest priority

  Here is the piece of code establishing the connection

  OleDbConnection oldbCnInfoLocal = new OleDbConnection();

  oldbCnInfoLocal.ConnectionString = "File Name = \\My Documents\\local.udl";

  oldbCnInfoLocal.Open();

  Here is the content of local.udl:

  [oledb]

  ; Everything after this line is an OLE DB initstring

  Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Finance;Data Source=PD820

  The machine name is PD820 (BTW, the machine has a Pentium D820 dual core processor).

  I have just used the Activity Monitor of SQL Server Management Studio and found one process for the application. Its Net Library is TCP/IP. What puzzles me is that its Login Time is 8/11/2006 7:52:29PM and Last Bath is 8/11/2006 9:12:31PM. The Last Batch time is certainly correct - it is whatever the time I click the Refresh button because the application is constantly querying the server. The application started in the mornig of 8/11/2006 and the connection was established then. As far as I am concerned, nothing happened at 8/11/2006 7:52:29PM.

  It seems what I should do if the error happens again is disabling all protocols except Shared Memory. I will..

  hz

  
  
  

• vcboy

  Thank Paul for pointing out the best practice of using connections.
  
  

• meighlough

  
  Yes, I believe that once you enable the shared memory protocol it always has a priority of 1.

  Actually, as a test I did disable all protocols and was still able to connect to a SQL Server 2000 database through SQLOLEDB using (local). I can only assume it's using the shared memory protocol. I don't know for certain whether the behavior has changed for SQL Server 2005.

  
  
  

• R.Working

  
  Are you maintaining a persistent connection to the database If so, I would recommend that you open and close connections only as needed instead. If your application is accessing the database frequently then it should benefit from connection pooling.
  
  

• WebService4Ever

  In some scenarios, just having shared memory enabled and set as the first protocol will not help. He is using SQLOLEDB, which will try to connect to SQL Server 2005 over TCP in some cases by default.

  See the blog link I referred to in my previous post. There is a lot of information there, but there's one specific comment related to this in the question and answer section. It says "MDAC OLEDB uses TCP protocol when you connect through (local) and localhost and in your case, your TCP/IP was disabled, you can verify this by enabling TCP/IP. However, if you specify <machinename>, it connects through shared memory." I don't think hipswich has posted if he's using the machine name or local, but if so, he will probably not be able to get a shared memory connection.

  Thanks,
  Sarah

  
  
  

• Jakein2006

  Thanks to all!

  Usually I get the following error first:

  "[DBNETLIB][ConnectionWrite(send()).]General network error. Check your network documentation." 

  I mistakenly thought it was generated by a statment accessing a remote SQL database.  I have just found out that it is probably generated by ExecuteScalar() of an OleDbCommand instance on a local SQL server.  It is very puzzling because there is no network involved in local connection.  Maybe the "network" in the error message has a very broad meaning.

  I checked the System events with Event Viewer and found the following four entries around the time (00:26) the above error happened last time:

  "The system detected that network adapter \DEVICE\TCPIP_{3CA0A752-BBE1-40EA-BF0F-BF5E704F127F} was connected to the network, and has initiated normal operation over the network adapter."

  "Your computer was not able to renew its address from the network (from the DHCP Server) for the Network Card with network address 0040F4E5A89D. The following error occurred:

  The operation was canceled by the user. . Your computer will continue to try and obtain an address on its own from the network address (DHCP) server."

  "The browser has forced an election on network \Device\NetBT_Tcpip_{3CA0A752-BBE1-40EA-BF0F-BF5E704F127F} because a master browser was stopped."

  "The system detected that network adapter \DEVICE\TCPIP_{3CA0A752-BBE1-40EA-BF0F-BF5E704F127F} was connected to the network, and has initiated normal operation over the network adapter."

  There was absolutely no hardware change at that time (I was sleeping).

  Any clue will be greatly appreciated.

   

  hz

  
  
  

• madswn

  Moving to the ".NET Framework Data Access and Storage" forum since the app uses ADO.Net.

  I wonder if it's related to connection pooling.

  
  
  
• Error "ExecuteScalar requires an open and available connection..."