Sql Server: "All I need is a little more time"
Our client is a windows form app. Our DAL is hosted by IIS on the same box as the sql server database. All select sprocs work perfect, no problems. The app connects to the DAL, which connects to Sql Server, Resultsets are passed back to the client through the remote type, no problems.
On the first call to do an insert or update sproc though we see this exception:
Index #0
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Source: .Net SqlClient Data Provider
Procedure: ConnectionRead (recv()).
Severity Level: -2
So, we try to do a save from the client again, same thing. We try a 3rd time and all works well over and over and over. No more timeouts. This happens every time, no matter the time of day or workload on the server, the first 2 times, then it always works fine.
This only happens on a sql server 2000 box. Our development box is sql server 7 and we never get a timeout exception.
Bob Beauchemin suggest I increaded the command timeout. So, rather than use the default 30, I explicitly set it to 60 right before the calls to Update on SqlDataAdapter. Know what? No more timeout exceptions and it only takes 1-2 seconds. This one baffles me as all I really did was explicitly set the CommandTimeout = 60 and no more exceptions on the fist 2 updates.
I would love to hear an explanation for this. Not sure if the sql server 7 vs. 2000 is why though it is a difference in the 2 environements.