Alex Rivera | Logout

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

Asked 2009-02-04T10:07:19.370
10

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-server visual-studio timeout
Edit
Report

2 Answers

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

answered 2013-03-21T21:26:55.680
1

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.

Cheers, Andy.

answered 2009-02-04T11:58:22.190

Your Answer