Command timeout

[Cath B]

I am pretty sure I am getting a command timeout when execute a SQL
procedure that has an output parameter. The code below is in an asp
page that is called using RSGetASPObject. I want to be able to send a
message back to the calling page to indicate that a timeout has
occurred, but am having a hard time capturing the timeout error. I
intend to set the timeout parameters so that I don't get timeout
errors, but in the case that I would I would like to know that is what
happened. Below is my remote page code that calls the SQL procedure.
Should I have the SQL procedure detect the timeout and send it back
since basically the output parameter is an error message (and how
would I do that?) or would the timeout be caught in the ASP code? (I
thought the "if err.number" code would catch it - but it doesn't
appear to.)

set objCommand = CreateObject("ADODB.Command")
objCommand.CommandTimeout = 1 'timeout for query
objCommand.CommandText = "p_svra_compare_report"
objCommand.ActiveConnection = ConnectString
objCommand.CommandType = adCmdStoredProc

'Input Parameters
objCommand.Parameters.Append objCommand.CreateParameter("@user_id",
adVarChar, adParamInput, 50, strCurrentUser)
objCommand.Parameters.Append
objCommand.CreateParameter("@call_type_ind", adVarChar, adParamInput,
5, strCallType)
objCommand.Parameters.Append objCommand.CreateParameter("@tab_name",
adVarChar, adParamInput, 50, strTabName)
objCommand.Parameters.Append
objCommand.CreateParameter("@page_number", adVarChar, adParamInput,
50, strPageNumber)
'Output Parameters
objCommand.Parameters.Append objCommand.CreateParameter("@errormsg",
adVarChar, adParamOutput, 255)
'Execute command
objCommand.Execute
if err.number <> 0 then
strErrMessage = "FALSE!" & Err.Description & "(Source: " &
Err.Source & ")"
CreateReport = strErrMessage
Exit Function
End If
strErrMessage = objCommand.Parameters("@errormsg").Value


Thank you

 

[Aaron Bertrand - MVP]

objCommand.CommandTimeout = 1 'timeout for query

Why on earth are you forcing this to be 1 second? Even if you have the most
efficient query in the world (e.g. SELECT 1) you still might go over one
second due to blocking, network activity, other ASP pages, etc. Try
increasing this to a more reasonable value like 10 or 30.

 

[Bob Barrows]

Why on earth are you forcing this to be 1 second? Even if you have
the most efficient query in the world (e.g. SELECT 1) you still might
go over one second due to blocking, network activity, other ASP
pages, etc. Try increasing this to a more reasonable value like 10
or 30.

I think she's trying to force the timeout to occur so she can see the error
that gets generated.

Bob Barrows

 

[Bob Barrows]

I am pretty sure I am getting a command timeout when execute a SQL
procedure that has an output parameter. The code below is in an asp
page that is called using RSGetASPObject.

What is that?

I want to be able to send a
message back to the calling page to indicate that a timeout has
occurred, but am having a hard time capturing the timeout error. I
intend to set the timeout parameters so that I don't get timeout
errors, but in the case that I would I would like to know that is what
happened. Below is my remote page code that calls the SQL procedure.
Should I have the SQL procedure detect the timeout and send it back
since basically the output parameter is an error message (and how
would I do that?) or would the timeout be caught in the ASP code?

The commandtimeout property is not visible to the stored procedure. If a
timeout occurs, it will happen before results are received from the
database, so the output parameter will not contain a value from the
procedure.

The command timeout should be caught by both the vbscript error handler and
the connection's Errors collection. Do you have the ADO documentation? It
can be found at msdn.microsoft.com/library.

(I
thought the "if err.number" code would catch it - but it doesn't
appear to.)

Why not? What happens when you run the code?

Bob Barrows