如何更改SQL Server中手动执行查询的超时时间?

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.

我有一个从Visual Studio手动执行的简单SQL脚本。这是一个数据生成脚本，所以我希望运行它需要几分钟。但是我得到了下面的错误。

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

超时过期。在操作完成之前或服务器没有响应的超时时间。

I don wan't 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?

我不需要改变任何全局服务器设置来运行这个文件。有什么命令可以放在文件的顶部以增加脚本/事务的超时吗?

7 个解决方案

#1

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)

如果您使用SQL Management Studio，选项中有两个设置(我指的是来自SQL Server 2005的Management Studio，我使用的就是它):

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)

在选项中，第一个在“查询执行”下，您可以在那里设置“执行超时”(我的已在0上)

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.

第二个选项(我认为这是您需要的)是“Designer”下的第一个选项，它表示类似于“对表设计器更新的覆盖超时”，您可以选中一个框并在其中放入一个值。不久前，我遇到了类似于您的问题(在大表上运行ALTER TABLE时出现超时消息)，我通过将该选项设置为更高的值来解决这个问题。

#2

Increasing the CommandTimeout property will solves the problem i.e.

增加CommandTimeout属性将解决如下问题:

SqlCommandObject.CommandTimeout = 500000

SqlCommandObject。CommandTimeout = 500000

#3

Increase the Query timeout and Connection timeout values in Visual Studio using the procedures documented below. Changing the Query Timeout:

使用下面记录的过程增加Visual Studio中的查询超时和连接超时值。更改查询超时:

In Visual Studio IDE, navigate to Tools -> Options ->Database Tools ->Query and View Designers You can either uncheck the option Cancel long running query or change the value of Cancel after option to a higher value. Changing the Connection Timeout:

在Visual Studio IDE中，导航到工具—>选项—>数据库工具—>查询和视图设计器，您可以取消选中选项，取消长时间运行的查询，或者将Cancel after选项的值更改为更高的值。改变连接超时:

In Visual Studio IDE, enable Server Explorer by navigating to View ->Server Explorer In the Server Explorer, right click on the connection to SQL Server where the CLR objects are being deployed and choose Modify Connection. Click on Advanced button on the Modify Connection window. In the Advanced Properties window change the Connect Timeout value under Initialization section to a higher value.

在Visual Studio IDE中，通过在服务器资源管理器中导航到视图->服务器资源管理器，启用服务器资源管理器，右键单击与正在部署CLR对象的SQL服务器的连接，并选择Modify connection。单击“修改连接”窗口上的高级按钮。在高级属性窗口中，将初始化部分下的Connect超时值更改为更高的值。

http://support.microsoft.com/kb/2011805

#4

SQL Server will wait indefinitely before returning to the user. More than likely there was a client side timeout property set. For example you can set a timeout property for the ADO command object.

SQL Server将无限期地等待，然后返回给用户。很可能设置了客户端超时属性，例如，可以为ADO命令对象设置超时属性。

Cheers, Andy.

干杯,安迪。

#5

@christian answer did not work for me (changing the settings in SQL Management Studio).

@christian answer对我不起作用(在SQL Management Studio中更改设置)。

Biswa answer worked for me. I will include code to clarify

比斯瓦回答对我起了作用。我将包括代码来澄清

SqlCommand cmd = new SqlCommand("MyReport", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;  /* note default is 30 sec */

#6

Never mind, I can run it fine with SQL Management Studio.

没关系，我可以用SQL Management Studio很好地运行它。

#7

Just wanted to clarify specific steps for Microsoft SQL Server Management Studio 2005... 'Designer' defaults definitely need to be adjusted even if it is set to '0' in 'Query Execution' section as 'Designer' vars override the 'Query Execution' settings.

只是想澄清Microsoft SQL Server Management Studio 2005的具体步骤……在“查询执行”部分，“设计器”的默认值必须调整为“0”，因为“设计器”vars覆盖“查询执行”设置。

Open Microsoft SQL Server Management Studio 2005
打开Microsoft SQL Server Management Studio 2005
Tools >> Options
工具> >选项
Expand 'Designers'
扩大“设计师”
I've found setting 'Transaction time-out after' to 120 has worked fine for updates to tables with several millions records.
我发现，将“事务超时”设置为120之后，可以对有数百万条记录的表进行更新。

#1