在 SQL Server 2005 中设置存储过程的查询超时 [英] Setting query timeout on a stored procedure in SQL Server 2005

问题描述

有谁知道如何设置存储过程的超时时间?在 NET 上找到了一些示例，例如 sp_configure 'remote Query Timeout', 5，但这不起作用.还发现了一些命令DBPROP_COMMANDTIMEOUT"和DBPROP_GENERALTIMEOUT"，但我不知道它们是否适合使用，如果它们适合，如何在我的事务 SQL 代码中使用它们.

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.

推荐答案

正如 Chris Tybur 所说，您不能在存储过程 或 SQL Server 上为存储过程查询超时.

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 是一个客户端概念:客户端会在一定时间后中止查询.对于存储过程中止自身/或任何查询，没有死人的计时器或机制).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.

远程查询超时"就是这样:超时当 SQL Server 进行远程调用时，当 SQL Server 本身是另一台服务器的客户端时.它在说明中说:

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.

最近有一个很好的问题:SQL Server 的超时设置

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

这篇关于在 SQL Server 2005 中设置存储过程的查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！