Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Increasing the Timeout Value for a SQL SERVER 2005 connection

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

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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