resolving a timeout error

resolving a timeout error

Post by John Crock » Sat, 19 Jul 2003 07:06:53


I'm fixing bugs and enhancing an application that has an
intermittent Timeout error ([Microsoft][ODBC SQL Server
Driver]Timeout expired) occasionally thrown during a the
execution of SQL queries (actually the execution of stored
procedures). I have a function that I call (passing the
SQL string), and inside that function is a Try/Catch
block. So I'm handling the error by displaying an error
message and putting the user back on the submitting page.

Two questions:
1) Do you know of any way I can get the ODBC driver to
throw this error, on demand? I need to throw the error in
a predicatble way, so I can test various solutions. I
tried things like turning sql off, etc. but I get
back "server not found" or "unable to connect" type
errors, not timeouts.

2) What are your ideas regarding handling timeouts, and
avoiding them in the first place? The crux of this
question is that I want to handle the timeout simply by
trying again. We only get this error once or twice a week
in an environment where this app is used all day, every
day by 30 ppl. I suspect that the timeouts come because
of some random issue on the network, or maybe the SQL
server itself. And I also suspect that if I could just
immediately try again, it would work (the second time
around), and the users would only see it as a response
time issue instead of as an error.

Thanks in advance for your help and ideas.

JC
 
 
 

resolving a timeout error

Post by DREW » Fri, 25 Jul 2003 19:50:45

Hello John

To answer your first question I use the following SQL Server Syntax

Waitfor delay '00:00:10'

Where the text string is HH:MM:SS.

For your second question I would run profiler on the server and watch the
SQL for each SPID and also capture Attention events and warnings. Timeouts
on the client force an Attention event on the server when the connection
goes away. This may show you the offending SQL.


cheers
Drew


Please do not send email directly to this alias. This alias is for
newsgroup purposes only.

This posting is provided "AS IS" with no warranties, and confers no rights