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.
