ADO.NET timeout

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have all kinds of stored procedures and queries need to run on the SQL
server. Because the database is big, most of the stored procedures and
queries will take more than one minute.

I always have a timeout problem when an operation needs to take more than 40
seconds to finish, no matter I use ExecuteNonQuery, ExecuteReader, or fill
the datatable through the SqlDataAdapter.

According to the artile (Article ID 229564,
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564), I set the
Polliing to false. But the problem still exits.

I doubt if it it the problem described in the article(Article ID 823679,
http://support.microsoft.com/default.aspx?scid=kb;en-us;823679).

If it is the problem described in article 823679, why Microsoft didn't fix
it in .NET 1.1 Service Pack 1.

Could somebody help me?
 
Hi,

Did you specify timeout for your command or not? If you specify it using
CommandTimeout property of the command then it should help you. If you did
not do this, then by default it will be set to 30 seconds
 
In addition to Val's good advice, you can also set the connection timeout in
the connection string, which is particularly handy, if you read in the
connection string from a configuration file. Simple specify the Connection
Timeout=xx attribute, where xx is the number of seconds.
 
Just a note here, that connection timeout and command timeout are different
things.
 
Hmm, I can't believe I misread that. Thanks for pointing it out.

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
Miha Markic said:
Just a note here, that connection timeout and command timeout are
different things.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

CT said:
In addition to Val's good advice, you can also set the connection timeout
in the connection string, which is particularly handy, if you read in the
connection string from a configuration file. Simple specify the
Connection Timeout=xx attribute, where xx is the number of seconds.
Click to expand...
Click to expand...
 
Thank you for your help. I tried the Connection timeout before. But I didn't
try command timeout. I'll let you know the result.

By the way, is the bug in Article (823679
http://support.microsoft.com/default.aspx?scid=kb;en-us;823679) fixed in
..NET1.1 Service Pack 1.

Miha Markic said:
Just a note here, that connection timeout and command timeout are different
things.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

CT said:
In addition to Val's good advice, you can also set the connection timeout
in the connection string, which is particularly handy, if you read in the
connection string from a configuration file. Simple specify the Connection
Timeout=xx attribute, where xx is the number of seconds.
Click to expand...
Click to expand...
 
Thank you both. It seems works. Once I set the command timeout, I can run a
90s query. The connection timeout cannot solve the problem.

Maybe Miha should publish an article about this issue. Because I cannot
remember any book or article emphasizes this issue. But it is really a
headache for me.

Once more question if you want to answer. Why Microsoft doesn't set the
Command timeout default to the connection timeout?

Thanks again!

Miha Markic said:
Just a note here, that connection timeout and command timeout are different
things.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

CT said:
In addition to Val's good advice, you can also set the connection timeout
in the connection string, which is particularly handy, if you read in the
connection string from a configuration file. Simple specify the Connection
Timeout=xx attribute, where xx is the number of seconds.
Click to expand...
Click to expand...
 
ConnectionTimeout and CommandTimeout are not related and do not have
anything in common. This is why they set into different values, because they
are designed for different purposes. ConnectionTimeout defines time, which
provider waits when application tries to open connection with the database.
If provider does not get response that connection is opened successfully
during this period of time, then it throws Connection Timeout exception.
CommandTimeout defines how much time provider waits until SQL statement will
finish it's execution

--
Val Mazur
Microsoft MVP


4city said:
Thank you both. It seems works. Once I set the command timeout, I can run
a
90s query. The connection timeout cannot solve the problem.

Maybe Miha should publish an article about this issue. Because I cannot
remember any book or article emphasizes this issue. But it is really a
headache for me.

Once more question if you want to answer. Why Microsoft doesn't set the
Command timeout default to the connection timeout?

Thanks again!

Miha Markic said:
Just a note here, that connection timeout and command timeout are
different
things.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

CT said:
In addition to Val's good advice, you can also set the connection
timeout
in the connection string, which is particularly handy, if you read in
the
connection string from a configuration file. Simple specify the
Connection
Timeout=xx attribute, where xx is the number of seconds.

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
Hi,

Did you specify timeout for your command or not? If you specify it
using
CommandTimeout property of the command then it should help you. If you
did not do this, then by default it will be set to 30 seconds

--
Val Mazur
Microsoft MVP


Hi,

I have all kinds of stored procedures and queries need to run on the
SQL
server. Because the database is big, most of the stored procedures
and
queries will take more than one minute.

I always have a timeout problem when an operation needs to take more
than 40
seconds to finish, no matter I use ExecuteNonQuery, ExecuteReader, or
fill
the datatable through the SqlDataAdapter.

According to the artile (Article ID 229564,
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564), I
set
the
Polliing to false. But the problem still exits.

I doubt if it it the problem described in the article(Article ID
823679,
http://support.microsoft.com/default.aspx?scid=kb;en-us;823679).

If it is the problem described in article 823679, why Microsoft
didn't
fix
it in .NET 1.1 Service Pack 1.

Could somebody help me?
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much. I understand it now.

Val Mazur said:
ConnectionTimeout and CommandTimeout are not related and do not have
anything in common. This is why they set into different values, because they
are designed for different purposes. ConnectionTimeout defines time, which
provider waits when application tries to open connection with the database.
If provider does not get response that connection is opened successfully
during this period of time, then it throws Connection Timeout exception.
CommandTimeout defines how much time provider waits until SQL statement will
finish it's execution

--
Val Mazur
Microsoft MVP


4city said:
Thank you both. It seems works. Once I set the command timeout, I can run
a
90s query. The connection timeout cannot solve the problem.

Maybe Miha should publish an article about this issue. Because I cannot
remember any book or article emphasizes this issue. But it is really a
headache for me.

Once more question if you want to answer. Why Microsoft doesn't set the
Command timeout default to the connection timeout?

Thanks again!

Miha Markic said:
Just a note here, that connection timeout and command timeout are
different
things.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

In addition to Val's good advice, you can also set the connection
timeout
in the connection string, which is particularly handy, if you read in
the
connection string from a configuration file. Simple specify the
Connection
Timeout=xx attribute, where xx is the number of seconds.

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
Hi,

Did you specify timeout for your command or not? If you specify it
using
CommandTimeout property of the command then it should help you. If you
did not do this, then by default it will be set to 30 seconds

--
Val Mazur
Microsoft MVP


Hi,

I have all kinds of stored procedures and queries need to run on the
SQL
server. Because the database is big, most of the stored procedures
and
queries will take more than one minute.

I always have a timeout problem when an operation needs to take more
than 40
seconds to finish, no matter I use ExecuteNonQuery, ExecuteReader, or
fill
the datatable through the SqlDataAdapter.

According to the artile (Article ID 229564,
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564), I
set
the
Polliing to false. But the problem still exits.

I doubt if it it the problem described in the article(Article ID
823679,
http://support.microsoft.com/default.aspx?scid=kb;en-us;823679).

If it is the problem described in article 823679, why Microsoft
didn't
fix
it in .NET 1.1 Service Pack 1.

Could somebody help me?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top