Where to add timeout?

Question

Where to add timeout?

adonetok

SSCrazy Eights

Points: 8395
More actions
June 4, 2014 at 7:13 am

#289508

I used code below in asp.net app to set up timeout for 10 seconds.
{
SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=DemoDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"usp_TestTimeOut";
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandTimeout = 10;
int restul = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
Is there a way to setup timeout in store procedure instead of in app code?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

adonetok (6/4/2014)
I used code below in asp.net app to set up timeout for 10 seconds.
{
SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=DemoDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"usp_TestTimeOut";
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandTimeout = 10;
int restul = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
Is there a way to setup timeout in store procedure instead of in app code?

No you can't do it on the sql side. In your code posted you have specified a connection timeout of 30 seconds and then overwritten that with your command timeout.

I would recommend you move your connection string to your config file so you don't have it hardcoded all over the place.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Timeout is a client-side concept. SQL has no idea what a timeout is, it will run queries for however long they take. It's client apps that get impatient and send timeouts.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass