Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP + SQL Server - Timeout Expired

Reply
Thread Tools

ASP + SQL Server - Timeout Expired

 
 
David Morgan
Guest
Posts: n/a
 
      10-01-2003
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.

 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      10-01-2003

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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Ersin Gençtürk ASP .Net 1 09-25-2007 07:33 AM
Urgent: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. VB Programmer ASP .Net 4 01-24-2006 04:48 AM
Re: "Timeout expired" for simple ADO.NET SQL Server query Nils Magnus Englund ASP .Net 0 09-15-2005 11:25 AM
"Timeout expired" for simple ADO.NET SQL Server query Nils Magnus Englund ASP .Net 3 09-12-2005 07:30 PM
Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Bob Johnson ASP .Net 0 08-07-2003 12:52 PM



Advertisments