Setting query timeout on a stored procedure in SQL Server 2005

Question

Does anyone know how to set the timeout on a stored procedure? Found some examples on the NET, e.g sp_configure 'remote Query Timeout', 5, but this did not work. Also found some commands "DBPROP_COMMANDTIMEOUT" and "DBPROP_GENERALTIMEOUT" but i don't know if they are the right ones to use and if they are, how to use them in my transact-SQL code.

Answer 1

As Chris Tybur said, you can not the the query timeout for a stored proc in the stored proc or on the SQL Server.

CommandTimeout is a client concept: the client will abort the query after a certain amount of time. There is no dead man's timer or mechanism for a stored proc to abort itself /or any query). SQL server will allow a query to run forever.

The "Remote Query Timeout" is exactly that: timeout when SQL Server makes a remote call, when SQL Server itself is the client of another server. It says in the description:

This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.

A recent question with good info: timeout setting for SQL Server

Answer 2

I have never heard of setting a timeout for executing a stored procedure on the server side. Usually you would specify the timeout for the command that runs the procedure in the data provider you are using, such as ADO.NET.