How do I pass timeout parameter to SQL Database Engine

Question

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?

Answer 1

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)

Answer 2

You can also set it connection-wide in the connection string.
"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30"

Answer 3

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.