ASP + SQL Server - Timeout Expired

D

David Morgan

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.
 
B

Bob Barrows

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top