Set Command Timeout from SQL Server 2005 rather than through the code?

Question

I have an issue where things are timing out and I need to get an intermediate fix up while I work on making it faster. I can apparently set Command Timeout on the code side and it will prevent this, but I am wondering if it's possible to set this on the SQL Server side so I can do it without doing a software patch.

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.commandtimeout.aspx

Says:

"This default value is set by the storage provider. Note that some providers may throw exceptions if this value is set to a non-zero value."

So how could I change the value of Command Timeout via the 'storage provider'? I assume it means SQL Server.

Answer 1

Command timeouts are always set by the client. Normally, this is configured when the connection is created, and sometimes the value is pulled from a DSN, an INI file or something similar to allow for re-configuration without having to recompile the client software.

The "Remote Query Timeout" that you will see through SSMS is meant to control the command timeout for queries that the SQL Server instance sends to to other database engines, not to control the timeout for queries sent to it. The "Remote Login Timeout" is similar, but applies to the amount of time a SQL Server will try to login to another instance, be it SQL Server, Oracle or something else.

Answer 2

Not clear on what is timing out but (and perhaps this has been done) you can set the remote query timeout in SQL Server under the Connection section of server properties in Mgt Studio. Right click on your server in Object Explore...Connections and change your remote query timeoout. Using sp_configure you may also change the remote query timeout (and the remote login timeout). Hope this helps