如何更改SQL Server中手动执行的查询的超时? [英] How can I change the timeout for a manually executed query in SQL Server?

问题描述

我有一个简单的SQL脚本，可以从Visual Studio中手动执行.这是一个数据生成脚本，因此我希望它需要花费几分钟才能运行.但出现以下错误.

I have a simple SQL script that I execute manually from Visual Studio. It is a data generation script so I expect it to take a couple of minutes to run. But I get the following error.

超时已过期.超时时间 完成之前已过去 操作或服务器未启动 响应.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

我不想更改任何全局服务器设置以能够运行此文件.我可以在文件顶部添加任何命令来增加该脚本/事务的超时时间吗?

I don't want to change any global server settings to be able to run this one file. Is there any commands that I could put at the top of the file to increase the timeout for just that script/transaction?

推荐答案

如果使用SQL Management Studio，则选项中有两个设置(我指的是SQL Server 2005中的Management Studio，我使用的是):
(我的Management Studio是德语的，所以希望我将名称正确翻译成英文)

If you use SQL Management Studio, there are two settings in the options (I'm referring to Management Studio from SQL Server 2005, which I use):
(my Management Studio is in German, so I hope I translated the names correctly into English)

您都可以在其他"->选项"下的菜单中找到

You can find both in the menu under "Extras" --> "Options"

在选项中，第一个位于查询执行"下，您可以在那里设置执行超时"(我的位置已经为零)

In the options, the first one is under "Query Execution", you can set the "Execution Timeout" there (mine was on zero already)

第二个(我认为这是您所需要的)是设计器"下的第一个选项，它表示覆盖表设计器更新的超时"之类的内容，您可以选中一个框并在其中输入一个值.
前一段时间，我遇到了一个与您类似的问题(在大型表上运行ALTER TABLE时出现超时消息)，我通过将此选项设置为更高的值来解决了该问题.

The second one (and I think this is what you need) is the first option under "Designer", it says something like "Override Timeout for table designer updates", you can check a box and put in a value there.
Some time ago, I had a problem similar to yours (timeout message when running ALTER TABLE on a large table), and I solved it by setting this option to a higher value.

这篇关于如何更改SQL Server中手动执行的查询的超时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！