469,903 Members | 1,461 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

ASP + SQL Server - Timeout Expired

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.

Jul 19 '05 #1
3 19568
Just a suggestion but the reference to adCmdText - have you got the necessary reference in the adovbs file?

Jack
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector - www.ciquery.com/tools/sqldirector/
"David Morgan" <da***@davidmorgan.me.uk> wrote in message news:#I*************@TK2MSFTNGP12.phx.gbl...
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.

Jul 19 '05 #2

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.
Jul 19 '05 #3
Sorry, but at the risk of double posting, I thought it valuable to add an entry to this post as I could not locate the other mentioned and Google brought me straight here in the first place.

Here is what I have found out in case anyone else is having similar issues.

I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.

After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index the procedure executed in < 1 second in QA and only a couple seconds from the web app.

Hope this helps anyone who's frustrated with similar issues.

Dave
Jun 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Hasan | last post: by
1 post views Thread by Dale | last post: by
3 posts views Thread by Nils Magnus Englund | last post: by
1 post views Thread by =?Utf-8?B?SmVycnkgSg==?= | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.