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)