在 SQL Server 2005 中设置存储过程的查询超时

Setting query timeout on a stored procedure in SQL Server 2005

提问by Johnny Lamho

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.

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

回答by gbn

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

正如 Chris Tybur 所说，您不能在存储过程或 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.

CommandTimeout 是一个客户端概念：客户端会在一定时间后中止查询。对于存储过程中止自身/或任何查询，没有死人的计时器或机制）。SQL 服务器将允许查询永远运行。

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:

“远程查询超时”就是：当 SQL Server 进行远程调用时超时，当 SQL Server 本身是另一台服务器的客户端时。它在描述中说：

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

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

回答by gbn

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?

另一个“为什么”：为什么要在“存储过程”上设置超时？为什么不是视图、函数或查询？您是否出于特定原因使用术语“存储过程”，或者您只是对学习如何在 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.

我问是因为我想知道您是否有锁定问题，也许 SET LOCK_TIMEOUT 1000 或 WITH(NOLOCK) 可能是您真正需要的。没有更多信息，虽然我不能说。如果您可以向我们提供更多关于您询问的原因、正在发生的事情以及在达到“超时”时您最终希望发生的事情的反馈，也许我们可以提供更多帮助。

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 :)

底线：是的，您可以在 T-SQL 中设置超时，是的，您可以使用 T-SQL 停止执行存储过程。但我不太了解您想提供关于在哪里查看或为您提供更多信息的建议。我有点害怕我已经说太多了:)

回答by Chris Tybur

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.

我从未听说过为在服务器端执行存储过程设置超时。通常，您会为在您使用的数据提供程序（例如 ADO.NET）中运行过程的命令指定超时。

回答by Dave Barker

This articlehas 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.

这篇文章很好地解释了查询超时以及它们是如何仅作为客户端概念的。您可以从工具|选项菜单在 SQL Server 管理工作室中设置查询超时。

回答by mrdenny

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.

您必须在客户端上执行存储过程时设置超时。就 SQL Server 而言，它将让存储过程永远运行，除非被告知取消它。