How can I change the timeout for a manually executed query in SQL Server?

Question

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?

Answer 1

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

SqlCommandObject.CommandTimeout = 500000

Answer 2

@christian answer did not work for me (changing the settings in 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 */

Answer 3

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)

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.

Answer 4

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

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:

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.

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