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.
+2
A:
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.
Chris Tybur
2009-09-01 00:44:14
Yes, it's a client timeout
gbn
2009-09-01 03:55:32
A:
This article has a very good explanation on query timeouts and how they're a client-side concept only. You can set the query timeout in SQL Server management studio from the Tools|Options menu.
Dave Barker
2009-09-01 00:48:42
A:
You have to set the timeout when you execute the stored procedure on the client. As far as the SQL Server goes, it'll let the stored procedure run for ever unless told to cancel it.
mrdenny
2009-09-01 03:08:22
+1
A:
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
gbn
2009-09-01 04:01:47
+1
A:
Wait - the real question is, "What is happening that you want to prevent?" Everyone has focused on server-side, client-side but in truth we don't know why you are asking this question (and it's important).
And another "why": why do you want to set a timeout on a "stored procedure"? Why not a view, a function, or a query? Did you use the term "stored procedure" for a particular reason or would you just be interested in learning how to set a timeout in T-SQL?
I'm asking because I wonder if you are having locking issues and perhaps SET LOCK_TIMEOUT 1000 or WITH(NOLOCK) might be what you really need. Without more info though I can't say. If you can give us more feedback on why you are asking, what is happening, and what ultimately you want to have happen if your "timeout" is reached, maybe we can help more.
Bottom line: Yes, you can set a timeout in T-SQL and yes you can stop the execution of a stored procedure with T-SQL. But I don't know enough about what you want to offer advice on where to look or to give you more info. I'm kinda scared that I've already said too much :)