19

I have a C# program which runs a stored procedure. If I run the stored procedure from Microsoft sql server management studio, it works fine. It does take about 30 seconds to execute. However, if I try to run the same stored procedure from a C# program, it times out, even though I have set the timeout in the connection string to 10 minutes.

using (connection1 = new SqlConnection("user id=user_id_goes_here;password=password_goes_here;initial catalog=database_name_goes_here;data source=server_name_goes_here;connection timeout=600))

It seem to time out after about 30 seconds, even though I have set it to allow 10 minutes (for testing purposes).

BenR
  • 11,296
  • 3
  • 28
  • 47
oshirowanen
  • 15,297
  • 82
  • 198
  • 350
  • 1
    How are you calling the procedure? Something that takes 30 seconds from management studio shouldn't take 10 minutes from C#, unless you've made a mistake somewhere that breaks indexes or statistics. – Joel Coehoorn Sep 25 '14 at 16:26

4 Answers4

29

The timeout on the connection is for connecting to the database only.

There is a separate CommandTimeout property of the SqlCommand class, use this property to specify the execution timeout.

Ie.

using (SqlCommand cmd = new SqlCommand())
{
  cmd.Connection = connection1;
  cmd.CommandTimeout = 240; //in seconds
  //etc...
}
Patrick Allwood
  • 1,822
  • 17
  • 21
  • 8
    If you use 0 this means no limit. – Juan Sep 24 '14 at 12:48
  • 2
    Just to point out that this is just a "band aid" for the problem. Better to figure out why you're getting a timeout in the first place by solving the underlying problem. – Chris Dunaway Sep 24 '14 at 20:47
  • 1
    @ChrisDunaway I assume you never worked on large databases? I had in projects SPs working for hours in the night integrating terabyte level data sets. Sometimes you just need time for things. – TomTom Sep 25 '14 at 12:06
  • 1
    @TomTom - Agreed, but what you describe sounds like the exception rather than the rule. Based on the OP, it doesn't seem likely that they're working with that much data. It's more likely that they just need good indexes and queries with good execution plans. – Chris Dunaway Sep 25 '14 at 13:12
6

Use SqlCommand.CommandTimeout property of your command instead of specifying it in connection string.

See MSDN for reference.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
2

You need to set it in code i.e by setting the CommandTimeout property of the sql command object.

The parameter 'connection timeout' in connection string represents the time to wait while trying to establish a connection before terminating the attempt and generating an error. It's not the time after which query execution will time out.

Thanks, I have also faced the same issue some weeks ago and was confused between the time out values in webconfig vs in command object. Your question cleared has my doubt now :)

reference link from msdn

Satyajit
  • 2,150
  • 2
  • 15
  • 28
1

Connection timeout refers to the amount of time permissible whilst actually connecting to SQL Server. Command timeout refers to how long is permissible for a command to run; in this case, a stored procedure. SqlCommand.CommandTimeout is the property you're looking for.

krisdyson
  • 3,217
  • 7
  • 43
  • 86