SQL Server timeout errors when using ADO
I work with databases of moderate size (a few hundred thousand records in a dozen or so tables, maybe 500 MB on disk.) I’m running against SQL Server 2000, which is supposedly an enterprise grade database. By the standards of Big Databases, this is pretty small potatoes, so it’s always a shock when SQL Server barfs. Sporadically, queries will fail with the following error:
Exception occured in Microsoft OLE DB Provider for SQL Server, Timeout expired. (8004e31)
(For your Googling pleasure I’m leaving “occurred” spelled wrong just like it is in the actual error message. The other thing to clarify here is the error number is actually a normal hex error code (0x80040E31) despite having been formatted to look like some wack-ass form of scientific notation.)
So most of the the Google hits on this topic yield various SQL cognoscenti telling various SQL newbies that their queries are lame, their network connection is bad, they should grow up and add an index to the table etc. Sometimes this is even valid advice.
But in some cases you just have a query that takes a Really Long Time To Execute. (Example: adding a new column with a default value to a table that contains 300,000 rows.) Queries like this run fine (if slowly) from the SQL Query Analyzer (which uses…ODBC!), but tank when called via ADO even if you set the Connection.Timeout property to 0 (= no timeout.)
Maddeningly more difficult to debug is that SQL Server occasionally stops answering queries for seconds(?) minutes(?) while it rearranges its sock drawer. I usually hear about this when one of my systems emails me an error log. In this case, it is not the query itself but SQL Server’s internal maintenance needs that cause the problem. The exact same query done a few minutes earlier or later would execute just fine. Since you never know when this is going to happen, you need to prevent the query from timing out after 30 seconds, which is the default.
It was thus nice to find DavidJ’s blog entry that reminded me that an ADO connection has both a Connection.ConnectionTimeout property (which I’ve been setting to increasingly huge numbers with no useful effect) and a Connection.CommandTimeout property. You need to set both of these to high values to avoid a timeout. So, in this case, the problem is ADO’s fault, not SQL Server’s fault. That means I can defer my rants about SQL Server to another day!
You can set these timeouts to 0 to indicate no timeout, but that’s bit dangerous since in some cases your timeout is actually is the result of a broken network connection and a timeout is desirable.
A shocking Windows discovery
The wretched Windows command line environment, cmd.exe, and its even more brain-damaged MS-DOS compatible cousin command.exe have received no small number of curses from me over the years. These shells are both necessary for certain Windows tasks and atrociously underpowered compared to even the most ancient and crusty UNIX shell.
But one of the things that has really bugged me with cmd.exe, given that it is running on a Windows machine and all, is that I can’t copy or paste commands/filenames/whathaveyou in and out of this window. This makes it very difficult to move the command line to specific directory currently open in Window/Internet Explorer. (This is especially true when the path is in the deeply buried “My Documents” folder.)
No doubt this is in part because cmd.exe needs to pass control keys directly into the buffer for DOS emulation, but it is partially because Microsoft writes lousy software. (You won’t be surprised that I think the best of all possible command-line worlds is OS X’s Terminal environment, where I can use Mac-like command keys to cut and paste while maintaining 100% compatibility with the control-key based unix world.)
But today I discovered if you right-click on the cmd.exe’s program icon in the Windows task bar that it has a few tricks up its sleeve. Right-clicking here provides a few commands: Edit, Defaults and Properties. It turns out that Properties allows you to change the window size and things like that. And if you delve into the Edit submenu, lo and behold—there are copy and paste. Of course the key bindings are nonexistent or hopelessly screwed up (“enter” for copy??) but when you’re stuck in the wilderness without food or water you have to be thankful when you find a rusty nail that can be fashioned into a fishhook.
Whenever I complain the utter worthlessness of the Windows command line, some wiseacre tells me to install cygwin. The problem with this is that 99% of the time I am working on of my remote ShotSpotter servers. These need to run 24x7 unattended for months and months. This is already hard enough to do using Microsoft’s own products; I can’t take the risk of installing a fifty megs of non-M$ approved utilities just to make typing a couple of commands easier.
(Un)helpful Windows error messages
In cleaning up some directories I came across this screenshot from one of my PCs:
Actually Linux and Mac OS X also come unhinged when they run out of disk space, but this error message has that Catch-22 flavor that only Microsoft can really get right.