0

I'm trying to run a stored procedure on SQL Server 2012 using a Windows service written in C#. The procedure returns no data, but takes a long time to execute, so I'm changing the CommandTimeout property of the SqlCommand to 5 minutes. Whenever I run the code though, I get a timeout error exactly 30s after execution started. Does anyone know why my value is being ignored, or is there another timeout that I've forgotten to set?

app.config file:

<configuration>
  <appSettings>
    <add key="myTimeout" value="600" />
  </appSettings>
  <connectionStrings>
    <add name="myConnectionString" connectionString=";data source=myServer,123456;Initial catalog=myDB;integrated security=SSPI;MultipleActiveResultSets=True;App=EntityFramework" />
  </connectionStrings>
</configuration>

Code:

var connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    cmd = new SqlCommand("usp_myLongRunningOperation", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // Change timeout if value present
    var commandTimeout = System.Configuration.ConfigurationManager.AppSettings["myTimeout"];
    int timeout;
    if (!string.IsNullOrEmpty(commandTimeout) && int.TryParse(commandTimeout, out timeout))
    {
        cmd.CommandTimeout = timeout;
    }

    cmd.ExecuteNonQuery();
}

(I've tested the timeout value parsing in isolation, and it's setting the value correctly)

leppie
  • 115,091
  • 17
  • 196
  • 297
Mourndark
  • 2,526
  • 5
  • 28
  • 52
  • http://stackoverflow.com/questions/3829202/iis-request-timeout-on-long-asp-net-operation – Ric Jun 09 '15 at 09:24
  • Sorry, typed web.config out of habit! This code is actually being run by a Windows service, IIS isn't involved. – Mourndark Jun 09 '15 at 09:31
  • 1
    Are you sure that the error is not generated by the SqlConnection.Open call? – Steve Jun 09 '15 at 09:33
  • 1
    Just another thought, is the config in the right place so that the value of `myTimeout` is actually the correct value? – Ric Jun 09 '15 at 09:41
  • @Steve There are two other stored procedures called before this one using the same method which are fine, so I'm assuming the Open() call works correctly for them. – Mourndark Jun 09 '15 at 09:51
  • @Ric I'll check, there are actually several async service calls between IIS services and Windows services so you may be right. – Mourndark Jun 09 '15 at 09:53
  • Not sure if this could help, but ... https://social.microsoft.com/Forums/en-US/e1689032-9cc1-4759-a994-c0fde272a8a2/commandtimeout-being-ignored-in-executereader-sql-server-2008r2-sp1-edit?forum=Offtopic – Steve Jun 09 '15 at 09:55
  • @Ric - Yes, the method call got moved from one service to another, and the appSetting got left behind! Thanks for the suggestions everyone. – Mourndark Jun 09 '15 at 10:19
  • 1
    @Mourndark good, glad it's sorted – Ric Jun 09 '15 at 10:27
  • Look at this [link](http://stackoverflow.com/questions/14451832/why-change-in-commandtimeout-not-working) – Leonardo Jun 09 '15 at 10:40

0 Answers0