I'm running a query that is timing out for first two times and returning results on the third time.
How do I tell SQL Server to wait until the query is completed instead of timing out?
Add a comment
|
3 Answers
By default, the SqlCommand.CommandTimeout is set to 30 seconds. You can assign a longer duration before executing the query, or set to 0 for no timeout. (if the question refers to .Net programming)
Comments
You can also set it connection-wide in the connection string.
"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30"
3 Comments
Santosh Chandavaram
thanks shane for your answer. devio's answer worked out too. will change connection string time out option too and see how it works.
John Saunders
@Shane: does connection timeout affect timeout of executing commands, or only just timeout of the connection open?
Shane Fulmer
My mistake - Connection Timeout is only a timeout for the initial connection to the database. Thanks for being polite about it. :)
Timeout is something controlled by the client application, not the server. It's the time the client is willing to wait for the response from the server.
Whatever package you are using to access the database will have a timeout value somewhere.
For example, within VBA with ADODB you could set the timeout to 60s as follows:
Dim cn As ADODB.Connection
Set cn = new ADODB.Connection
cn.CommandTimeout = 60
As Shane pointed out above, you can set it in the connection string as well.
I think you can set it to 0, so that it will never timeout, but this not recommended as it means if the server hangs or you lose the DB connection, your application will wait forever.
