Solved

Increasing the Timeout Value for a SQL SERVER 2005 connection

Posted on 2008-06-23
5
956 Views
Last Modified: 2013-12-16
Hello Experts,

I am calling a Stored Procedure from a C# application. It works fine mostly but the connection times out when the dataset gets large.

Is there a way to reset the default timeout value to increase the time before the connection times out in SQL Server 2005?

Thanks!
0
Comment
Question by:Saxitalis
  • 3
  • 2
5 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 21852265
Hi Saxitalis;

There are two timeouts and I am not sure which one you are talking about.

The first of the two is:
The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

This timeout is set in the connection string and its value is in seconds, the default is set to 15 seconds. The value is, Connect Timeout = NumberOfSeconds; or, Connection Timeout = NumberOfSeconds; either way will do.

The second of the two is when the connection has been made and you have executed a command:
Gets or sets the wait time before terminating the attempt to execute a command and generating an error.  The default timeout is 30 seconds.

SqlCommand.CommandTimeout = NumberOfSeconds;

Where SqlCommand is the instance variable of the SQL Command.

Fernando

 

0
 

Author Comment

by:Saxitalis
ID: 21852320
Thanks Fernando,

I will give this a try. My situatuin is #2 (SqlCommand.CommandTimeout = NumberOfSeconds;
). Is it better to put it before or after the executing the command?

0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 21852416
Needs to be before executing the command.
0
 

Author Closing Comment

by:Saxitalis
ID: 31470024
Perfect! Thanks
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 21880630
Not a problem, glad I was able to help. ;=)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 35
how to just get time from a date 6 31
VB.net Duplicating a table - primary key not created 3 31
Input parameteres to DragOver 2 19
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question