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.
LVL 1
shieldguyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel WilsonConnect With a Mentor Commented:
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
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.

All Courses

From novice to tech pro — start learning today.