Command timeout

Discussion in 'ASP General' started by Cath B, Dec 19, 2003.

  1. Cath B

    Cath B Guest

    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
     
    Cath B, Dec 19, 2003
    #1
    1. Advertising

  2. > 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/
     
    Aaron Bertrand - MVP, Dec 19, 2003
    #2
    1. Advertising

  3. Cath B

    Bob Barrows Guest

    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.
     
    Bob Barrows, Dec 19, 2003
    #3
  4. Cath B

    Bob Barrows Guest

    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.
     
    Bob Barrows, Dec 19, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bob Johnson
    Replies:
    0
    Views:
    3,776
    Bob Johnson
    Aug 7, 2003
  2. Do
    Replies:
    2
    Views:
    6,383
  3. Guoqi Zheng
    Replies:
    4
    Views:
    13,036
    Guoqi Zheng
    Jun 3, 2004
  4. Just D

    IIS timeout, IE timeout or what?

    Just D, Jun 21, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    3,534
    Just D
    Jun 21, 2004
  5. Mark Probert

    Timeout::timeout and Socket timeout

    Mark Probert, Oct 6, 2004, in forum: Ruby
    Replies:
    1
    Views:
    1,293
    Brian Candler
    Oct 6, 2004
Loading...

Share This Page