How can I change the timeout for a manually execut

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 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?

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

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

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

Answer 4

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.

1. Open Microsoft SQL Server Management Studio 2005
2. Tools >> Options
3. Expand 'Designers'
4. I've found setting 'Transaction time-out after' to 120 has worked fine for updates to tables with several millions records.

Answer 5

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

SqlCommandObject.CommandTimeout = 500000

Answer 6

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