[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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