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?
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.
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.
Increasing the CommandTimeout property will solves the problem i.e.
SqlCommandObject.CommandTimeout = 500000