Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Increasing the Timeout Value for a SQL SERVER 2005 connection

Posted on 2008-06-23
5
Medium Priority
?
962 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 64

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 64

Accepted Solution

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

Author Closing Comment

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

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

782 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