Unanswered: error #53, connectiontimeout takes 7 minutes to return error
First post on your forum. My name is Tanner and i'm a VB developer from North Carolina, USA. I have encountered an issue which I am unable to find an answer so I figured I would ask your community here in the hopes that someone can clarify what I don't understand.
So my question is, how do I configure my remote sql server box to send back an error #53 (not available error) within 2 seconds when the machine is not on the network?
Project notes and troubleshooting steps taken:
I have been working on a project at work which contains a common data access class for all the forms to use in that project. The program has many sql calls to make, and all of them are done on a remote sql box (sql 2005 version). I wanted to consider many different testing scenarios including "what happens if the network isn't available?" so I unplugged the sql box from the network and tried a few calls to see what would happen. My guesses were that my connection to the sql machine would time out and I would catch a sql exception returned to my program with error #53 attached.
My guesses were partly correct, this is the error I expected, but it takes my application 7 minutes to time out and throw this error back to me. After observing this I wondered how Sql Server Management Studio would behave in a similar situation. It also takes about 7 minutes to bring me back the error that tells me the remote machine is not available. a semi important note: my boss's development machine takes more than a minute to do the same task, but not nearly as long as my machine takes.
I began adjusting timeout values on the server box first, I adjusted "remote query timeout" from 600 down to 2, restarted MSSQLSERVER service, and tested again. No changes. I went into the tools menu -> Options and then under "Query Execution" changed around the execution time-out value. What I found super annoying is the fact that the "connect to server" form has the "options" button that expands it to show tabs. On one of these tabs is a connection timeout value that is set to 15 by default. No matter how short or long of a value I entered there, it took 7 minutes for the timeout message to come back to me.
At some point it dawned on me that if the entire box is unplugged from the LAN, then there's no way for the remote machine to know about timeout values, because it doesn't actually exist out there. So I changed these timeout values on the local machine that runs the application/Management Studio.
This is the point I am up to. I am not sure why the timeout values seem to be taking a mind of their own. Since all of my sql calls are implemented in a common data access class in my application I would like to time out the connection after a couple seconds if it does not connect and set some type of "sqldisconnected" variable to True.
After a lot of researching today I have seen some blog entries that lead me to believe that remote sql server connections RELY on you having a solid LAN connection. What makes this even more strange is that a lofty query will result in a timeout if the processing takes enough time (ie: the normal expected behavior), yet when you pull the cable out of the back it stops behaving.
I figure that there is some other place I need to configure the timeout in order to achieve the functionality I am after. I guess it's also a possibility that I am trying to force functionality where it does not exist, in which case I have written a routine which pings the remote server and then checks to see if the sql server service is running. This approach works, but seems extra unweildy when all this timeout and error handling exists out of the box.
hopefully someone out there has fought with this before and knows the solution.
it is 15 seconds in management studio under the "connection" tab on "connect to server" window, but no matter what number I put there the result is always about 7 minutes to return the error that the machine is not on the network.