Solved

Increasing the Timeout Value for a SQL SERVER 2005 connection

Posted on 2008-06-23
5
958 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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