469,640 Members | 1,561 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,640 developers. It's quick & easy.

Command timeout

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
Jul 19 '05 #1
4 11295
> 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.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #2
Aaron Bertrand - MVP wrote:
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.


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

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #3
Cath B wrote:
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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #4
cb
Bob,
Thank you for your reply. After you told me that I should get an error
I looked harder at my code and found that I didn't have "on error resume
next" so I added it and did get a message when I force a timeout.

Thanks again.

Cat B

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jawahar Rajan | last post: by
3 posts views Thread by rh0dium | last post: by
2 posts views Thread by twigster | last post: by
8 posts views Thread by Thierry Lam | last post: by
2 posts views Thread by John Kotuby | last post: by
2 posts views Thread by Gil_H | last post: by
1 post views Thread by alexcpn | last post: by
reply views Thread by Omar Abid | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.