SQL server Timeout
-
hello techies, I written a C# application which executes few stored procedures against the database(MSsql 7.0) .While executing stored procs thru my appln, sometimes it throws sql exception saying "Time expired when running the scripts". But it happens only 2 out of every 10 test runs.. Is there a way to overcome this problem rgds, jagan ps; I donot have that many connections to SQL server as well.
-
hello techies, I written a C# application which executes few stored procedures against the database(MSsql 7.0) .While executing stored procs thru my appln, sometimes it throws sql exception saying "Time expired when running the scripts". But it happens only 2 out of every 10 test runs.. Is there a way to overcome this problem rgds, jagan ps; I donot have that many connections to SQL server as well.
Time your stored procedure. One very simplistic way is something like this:
DateTime start = DateTime.Now;
try
{
connection.Open();
command.ExecuteNonQuery(); // or whatever is appropriate
}
finally
{
connection.Close();
TimeSpan span = DateTime.Now - start; // span holds the time it took to exec.
}Again, this is very simplistic. If you need to adjust your timeout for the command (not for the connection, which probably isn't the problem) - and do so intelligently since changing the timeout for invalid commands can make your program take needlessly longer to return in err - then change the
CommandTimeoutproperty of whateverIDbCommandimplementation you're using (likeSqlCommand). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] -
Time your stored procedure. One very simplistic way is something like this:
DateTime start = DateTime.Now;
try
{
connection.Open();
command.ExecuteNonQuery(); // or whatever is appropriate
}
finally
{
connection.Close();
TimeSpan span = DateTime.Now - start; // span holds the time it took to exec.
}Again, this is very simplistic. If you need to adjust your timeout for the command (not for the connection, which probably isn't the problem) - and do so intelligently since changing the timeout for invalid commands can make your program take needlessly longer to return in err - then change the
CommandTimeoutproperty of whateverIDbCommandimplementation you're using (likeSqlCommand). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles]Heath Stewart wrote: invalid commands can make your program take needlessly longer to return in err Heath - what do you mean by "invalid command"? The call to the DB should return as soon as the DB is done processing it, regardless of the timeout right? So if the command is invalid, wouldn't the DB return some type of syntax error immediately? Well, now that I think about it, if the application allowed the user to enter an arbitrary query, I guess the user could do something silly that took forever to execute which would effectively hang the app until the timeout was reached. Is this the type of situation you are referring to? Brian
-
Heath Stewart wrote: invalid commands can make your program take needlessly longer to return in err Heath - what do you mean by "invalid command"? The call to the DB should return as soon as the DB is done processing it, regardless of the timeout right? So if the command is invalid, wouldn't the DB return some type of syntax error immediately? Well, now that I think about it, if the application allowed the user to enter an arbitrary query, I guess the user could do something silly that took forever to execute which would effectively hang the app until the timeout was reached. Is this the type of situation you are referring to? Brian
Brian Nottingham wrote: Well, now that I think about it, if the application allowed the user to enter an arbitrary query Unless you're writing some database management tool, an application should never let a user enter an arbitrary query. There should always be some kind of control over what the user can enter. "Invalid commands" may be a little overly broad but consider cases where a stored proc might connect to a remote data store (another database, XML, etc.). "Invalid command", depending on the scope of the problem, could also mean that the condition is to general and would select too many records for the intended use of the data. It doesn't necessary mean there's a syntax error. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles]
-
Brian Nottingham wrote: Well, now that I think about it, if the application allowed the user to enter an arbitrary query Unless you're writing some database management tool, an application should never let a user enter an arbitrary query. There should always be some kind of control over what the user can enter. "Invalid commands" may be a little overly broad but consider cases where a stored proc might connect to a remote data store (another database, XML, etc.). "Invalid command", depending on the scope of the problem, could also mean that the condition is to general and would select too many records for the intended use of the data. It doesn't necessary mean there's a syntax error. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles]
Heath Stewart wrote: an application should never let a user enter an arbitrary query Totally agree. Heath Stewart wrote: select too many records for the intended use of the data I guess thats what i was getting at, but with the added twist that the user was allowed to enter it. The programmer should have an idea of the data the database may contain, and write the queries appropriately. If you know its going to take a while, increase the timeout. Which is what you already said a few posts ago, so... yeah... Brian
-
Heath Stewart wrote: an application should never let a user enter an arbitrary query Totally agree. Heath Stewart wrote: select too many records for the intended use of the data I guess thats what i was getting at, but with the added twist that the user was allowed to enter it. The programmer should have an idea of the data the database may contain, and write the queries appropriately. If you know its going to take a while, increase the timeout. Which is what you already said a few posts ago, so... yeah... Brian
Unfortunately, most programmers are human and not only make mistakes but can't account for every possibility. Many uses for Code Access Security are to ensure that code isn't used inappropriately - protection against such a check. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles]
-
Unfortunately, most programmers are human and not only make mistakes but can't account for every possibility. Many uses for Code Access Security are to ensure that code isn't used inappropriately - protection against such a check. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles]