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'remight Query Timeout'，5，但这不起作用。还发现了一些命令“DBPROP_COMMANDTIMEOUT”和“DBPROP_GENERALTIMEOUT”，但我不知道它们是否是正确的使用方法，如果它们是，如何在我的transact-SQL代码中使用它们。
5 个解决方案
#1
5
As Chris Tybur said, you can not the the query timeout for a stored proc in the 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 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:
“远程查询超时”正是如此：当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的超时设置
#2
3
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。
#3
3
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停止执行存储过程。但是我不知道你想要提供什么建议，或者给你更多信息。我有点害怕我已经说得太多了:)
#4
1
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.
本文对查询超时以及它们仅仅是客户端概念有很好的解释。您可以从“工具”|“选项”菜单中在SQL Server管理工作室中设置查询超时。
#5
0
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而言，它将让存储过程永远运行，除非被告知取消它。
#1
5
As Chris Tybur said, you can not the the query timeout for a stored proc in the 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 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:
“远程查询超时”正是如此：当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的超时设置
#2
3
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。
#3
3
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停止执行存储过程。但是我不知道你想要提供什么建议，或者给你更多信息。我有点害怕我已经说得太多了:)
#4
1
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.
本文对查询超时以及它们仅仅是客户端概念有很好的解释。您可以从“工具”|“选项”菜单中在SQL Server管理工作室中设置查询超时。
#5
0
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而言，它将让存储过程永远运行，除非被告知取消它。