Changing the Commandtimeout in SQL Management Studio

Changing the CommandTimeout in SQL Management studio

Changing Command Execute Timeout in Management Studio:

Click on Tools -> Options

Select Query Execution from tree on left side and enter command timeout in "Execute Timeout" control.

Changing Command Timeout in Server:

In the object browser tree right click on the server which give you timeout and select "Properties" from context menu.

Now in "Server Properties -....." dialog click on "Connections" page in "Select a Page" list (on left side). On the right side you will get property

Remote query timeout (in seconds, 0 = no timeout):
[up/down control]

you can set the value in up/down control.

Increasing the Command Timeout for SQL command

it takes this command about 2 mins to return the data as there is a lot of data

Probably, Bad Design. Consider using paging here.

default connection time is 30 secs, how do I increase this

As you are facing a timeout on your command, therefore you need to increase the timeout of your sql command. You can specify it in your command like this

// Setting command timeout to 2 minutes
scGetruntotals.CommandTimeout = 120;

manually increasing SQL server command timeout

I don't think there's any easy way to set this globally - e.g. in the connection string or something.

What you could do (but this requires your code to work that way) is put your command timeout into configuration:

<configuration>
   <appSettings>
      <add name="CommandTimeout" value="120" />
   </appSettings>
</configuration>

and then in your code, read that value from configuration and use it for your SqlCommand classes:

int commandTimeout = Convert.ToInt32(ConfigurationManager.AppSettings["CommandTimeout"]);

using(SqlCommand cmd = new SqlCommand(..(your SQL statement)..., connection))
{
    cmd.CommandTimeout = commandTimeout;
}

With this, you could at least tweak the timeout without having to change your code.

SQL Server TimeOut in C# After Adjusting timeout

Connection timeout and command timeout are two different things.

A connection timeout occurs when a connection cannot be retrieved from the connection pool within the allotted timeout period.

A command timeout occurs when a connection has been retrieved, but the query being executed against it doesn't return results within the allotted command timeout period. The default command timeout period in ADO.NET is 30 seconds.

If you've set the connection timeout to 300 seconds and you're still getting a timeout, it's likely a command timeout. As walkers01 said, set your command timeout to a suitable number of seconds. 300 seconds should be far more than sufficient; if your query executes in one minute in SSMS, a timeout of 90 seconds should suffice.

Why change in CommandTimeout not working

The timeout you set here is the timeout for the driver you use. Maybe you have a timeout setting in your SQL server itself. Open the SQL management studio, connect to the SQL server and right click on properties to check under options if you set another timeout there. Set it to your prefered value there.

Default Timeout Period in SQL Server

DbCommand has CommandTimeout, which is what you want here - it is set per command; the "connect timeout" only impacts, reasonably enough, what the timeout is for connecting. The default value for CommandTimeout on SqlCommand is 30 seconds.

Set commandtimeout for linked SQL Tables/Views in Access front end

There is an ODBC timeout property. Open the query in design view, and go to properties to see it. There is also an (ODBC) query timeout on the current database properties page. You can set it programmatically as well:

Dim objDB As DAO.Database
Set objDB = CurrentDb()
objDB.QueryTimeout = 120   

http://www.geeksengine.com/article/how-to-change-timeout-value-for-access-sql.html

Also check the server configuration. There is a query timeout on server side.

---

Related Topics