ASP + SQL Server - Timeout Expired

Discussion in 'ASP General' started by David Morgan, Oct 1, 2003.

  1. David Morgan

    David Morgan Guest

    Hello

    In my ASP page I am trying to ascertain whether a randomly generated ID starts with certain characters that are not allowed or has been used before.

    When the SQL statement for latter is executed I get:

    Microsoft OLE DB Provider for SQL Server error '80040e31'

    Timeout expired

    /Admin/Start2WaySMS.asp, line 52

    This is most bizarre because I have already executed two SQL statements prior to this on the same connection object, both of which execute successfully.

    Also, this is only happening on my production server and not my staging server. Both servers are at the same MDAC revision and SQL Server 2000 SP3.

    This is the ASP code that is being executed:

    bSessionIDOK = False

    Do While Not bSessionIDOK

    stSessionID = Generate2WaySessionID

    If objConn.Execute("SELECT Keyword FROM dbo.tblReservedKeywords WHERE (Keyword = " & DbString(Left(stSessionID, 2)) & ")", , adCmdText).EOF & _
    And objConn.Execute("SELECT SessionID FROM dbo.tbl2WaySessions WHERE (SessionID = " & DbString(stSessionID) & ")", , adCmdText).EOF & _
    Then bSessionIDOK = True

    Loop

    The line that reports the error is where the second statement is executed.

    I have watched this execute using SQL Profiler and nothing untoward is reported, save a sp_reset_connection immediately after the second statement appears. Most statements have about four lines in profiler that have the same text and different event classes. e.g. SQL:BatchStarting, SQL:StmtStarting, SQL:StmtCompleted and SQL:BatchCompleted. All that is reported for the statement in error is: SQL:BatchCompleted, Attention and RPC:Completed.

    Naturally, I have run the statements themselves in Query Analyzer and they both execute with no problems. One thing I should mention is that there are no records in tbl2WaySessions. (I know that if I was using a stored procedure I would get a closed recordset, not EOF.)

    I have also changed my code to assign the objConn.Executes to recordset variables and then test their EOFs (closing the first one before opening the second) and exactly the same thing happens.

    I have tried closing and opening the connection before the loop starts, and setting the cursor location to be adUseServer. Normally I don't set anything so it will be using the defaults of ReadOnly and ForwardOnly.

    I have changed the problematic statement into a stored procedure that returns 1 or 0 and tested for that value and again I get the same time out error.

    Create Procedure usp_IsUniqueSessionID
    (
    @SessionID char(16)
    )
    As
    set nocount on
    DECLARE @Result int
    if EXISTS(SELECT SessionID FROM dbo.tbl2WaySessions WHERE (SessionID = @SessionID))
    BEGIN
    SET @Result = 1
    END
    else
    SET @Result = 0

    SELECT 'IsUniqueSessionID' = @Result
    return

    I have run sp_Who2 and there is no blocking.

    I am sure I have missed something stupid but cannot figure it out. Any help would be much appreciated. Just off to check that SessionID is not a reserved word or something... which it isn't.

    David M

    P.S. Apologies for posting in HTML but I am using Outlook Express and I know a plain text version will also be available for those with down-level news readers and those accessing via a website. Hopefully this will be more readable for those viewing this with software released this century.
     
    David Morgan, Oct 1, 2003
    #1
    1. Advertising

  2. David Morgan

    Bob Barrows Guest

    Please don't multipost. It's going to very difficult to
    follow this conversation in two newsgroups. Since it's a
    database-related question, let's carry on the conversation
    over in asp.db where I've already posted two replies.

    Thank you.
     
    Bob Barrows, Oct 1, 2003
    #2
    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,826
    Bob Johnson
    Aug 7, 2003
  2. Nils Magnus Englund

    "Timeout expired" for simple ADO.NET SQL Server query

    Nils Magnus Englund, Sep 12, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    16,810
    Kevin Spencer
    Sep 12, 2005
  3. Nils Magnus Englund
    Replies:
    0
    Views:
    796
    Nils Magnus Englund
    Sep 15, 2005
  4. Pablo Q.
    Replies:
    0
    Views:
    167
    Pablo Q.
    Sep 18, 2008
  5. sheng chen

    SQL Server timeout expired

    sheng chen, Apr 27, 2011, in forum: Ruby
    Replies:
    0
    Views:
    151
    sheng chen
    Apr 27, 2011
Loading...

Share This Page