• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

How to increase the server timed out period

I am running a database query in C# that extracts the data from SQL Server. I am using a data adapter and when I try to fill it using da.fill command it raises an exception saying "Timeout expired. The timeout period elapsed prior to the completion of the operation or the server is not responding. All the other queries are working fine, this is the only query which is causing the problem. when i run this query on SQL query analyzer it works fine though take long time. Is there any way I can increse the server respond time.
0
shieldguy
Asked:
shieldguy
  • 4
  • 3
  • 2
1 Solution
 
Daniel WilsonCommented:
Yes, you need to increase the CommandTimeout on your Command object.

Can you post the relevant code?
0
 
shieldguyAuthor Commented:
System.Data.SqlClient.SqlDataAdapter vDA;
DataSet ds = new DataSet();
            try
            {
                if (vCnSQL.State == ConnectionState.Closed) { vCnSQL.Open(); }
                vDA.SelectCommand.CommandText = Query;
                vDA.Fill(ds);
            }
            catch (Exception e)
            {
                Log("", e);
                return null;
            }
            finally
            {
                if (vCnSQL.State != ConnectionState.Closed)
                {
                    vCnSQL.Close();
                }

            }
            return ds;
0
 
shieldguyAuthor Commented:
I have tried increasing the commandtimeout but still it's causing the problem. Plesae have a look at the code

public void init(int connIndex)
        {
            string cnStr = GetConnectionString(connIndex);
            vCnSQL = new System.Data.SqlClient.SqlConnection(cnStr);
            vCmd = new System.Data.SqlClient.SqlCommand();
            vCmd.Connection = vCnSQL;
            vCmd.CommandTimeout = 1000000;
            vDA = new System.Data.SqlClient.SqlDataAdapter();
            vDA.SelectCommand = new System.Data.SqlClient.SqlCommand();
            vDA.SelectCommand.Connection = vCnSQL;
        }
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Anthony PerkinsCommented:
Perhaps I am being dense, but where are you using the query? And don't you think that 11 days is excessive for a CommandTimeout.  Why not just set it to 0 (infinite) and then it will be more obvious as to your intent.  But first you need to set the CommandTimeout property to the correct SQLCommand object.
0
 
shieldguyAuthor Commented:
set it to 0? I didn't understand that. Do we need to increase the timeout or decrease it?
0
 
Daniel WilsonCommented:
Setting it to 0 means making it infinite.

You're setting it on the wrong command object.  Try this:

public void init(int connIndex)
        {
            string cnStr = GetConnectionString(connIndex);
            vCnSQL = new System.Data.SqlClient.SqlConnection(cnStr);
            vCmd = new System.Data.SqlClient.SqlCommand();
            vCmd.Connection = vCnSQL;
 
            vDA = new System.Data.SqlClient.SqlDataAdapter();
            vDA.SelectCommand = new System.Data.SqlClient.SqlCommand();
            vDA.SelectCommand.Connection = vCnSQL;
            vDA.SelectCommand.CommandTimeout = 1000000;
        }

Open in new window

0
 
Anthony PerkinsCommented:
>>set it to 0? I didn't understand that.<<
0 = Infinite.  The same setting used by SQL Server Query Analyzer by default.  IMHO using 1000000 is confusing and you might as well set it to 0.  It is essentially the same.

But the big question is why are you resorting to increasing the Command Timeout. Have you tried optimizing the query?
0
 
Daniel WilsonCommented:
>> IMHO using 1000000 is confusing and you might as well set it to 0.

Agreed.
0
 
shieldguyAuthor Commented:
Thanks very much for your help!! It worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now