SQL OLEDB - Timout Problem w/ Particular Statement

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

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.

[Bob Barrows]

I suggest converting this to a stored procedure. Your code is very
inefficient:
1. you make two trips to the database where one will do.
2. You have two recordsets being returned to your page, either of which
could contain some amount of data, which you don't even intend to use.

Personally, I would create a stored procedure with an output parameter,
however, I don't have time to illustrate how to do it now, so I will show
you a stored procedure that returns a recordset containing the result of the
test:

CREATE PROCEDURE KeywordExists (
@word varchar(30)) AS
IF EXISTS (Select * FROM dbo.tblReservedKeywords WHERE Keyword = Left(@word,
2))
AND EXISTS
(Select * FROM dbo.tbl2WaySessions WHERE SessionID = @word)
Select 1 KeywordExists
Else
Select 0 KeywordExists


In ASP:
bSessionIDOK = False
Do While Not bSessionIDOK
stSessionID = Generate2WaySessionID
set rs=server.createobject("adodb.recordset")
objConn.KeywordExists stSessionID, rs
If rs(0).value = 0 Then bSessionIDOK = True
rs.close
set rs = nothing
Loop

HTH,
Bob Barrows


"David Morgan" <david@davidmorgan.me.uk> wrote in message
news:O62HJiEiDHA.1952@TK2MSFTNGP10.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.)
This is incorrect.

[Bob Barrows]

For even more efficiency, would it be possible to put the
code that generates the random ID into the stored
procedure so that you're not making all the trips to the
database that this loop may require?

Bob Barrows

>-----Original Message-----
>
> Do While Not bSessionIDOK
>
> stSessionID = Generate2WaySessionID

[David Morgan]

Hi Bob

Thanks for your most hasty response, it is much appreciated.

Whilst your performance optimisations are noted I am afraid that doesn't
actually help me with this exact problem.

It is the most bizarre thing I have ever seen.

My next step was to create a stored procedure which just did a check for
EXISTS on the @Word. This too returned timeout expired whilst the
previous statement, (checking the reserved words table), executes with
no problem.

So I stepped even further back and just did a SELECT * FROM table in a
completely new ASP. This too returns me Timeout Expired as per the
previous attempts.

Here is the code for this new page:

------------------------------------------------------------
<% Option Explicit
Dim objConn, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("Database_ConnectionString")
Set objRs = objConn.Execute("SELECT * FROM dbo.tbl2WaySessions")
If Not objRs.EOF Then
Do While Not objRs.EOF
Response.Write "<p>" & objRs.Fields(0).Value
objRs.MoveNext
Loop
End If
objRs.Close
'Response.Write "<p>" & objConn.Execute("EXEC usp_IsUniqueSessionID
'36B4PS3293I00NYQ'").Fields(0).Value
objConn.Close
Set objConn = Nothing %>
------------------------------------------------------------

Now whilst this again might be inefficient, I am starting to really
loose my marbles with this problem.

As previously stated, the connection string is being used on 100 other
pages with no problems.

Again, any help would be most welcomed.

Regards

David M


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

You may be running into something called "parameter sniffing". Here is a
good explanation of it:
[url]http://tinyurl.com/h7aa[/url]

You should use Profiler to see if a different execution plan for your query
is used when calling it from asp and when calling it from QA. This will be a
pretty good indication that you need to take the steps suggested in the
link.

Bob Barrows

David Morgan wrote:

> Hi Bob
>
> Thanks for your most hasty response, it is much appreciated.
>
> Whilst your performance optimisations are noted I am afraid that
> doesn't actually help me with this exact problem.
>
> It is the most bizarre thing I have ever seen.
>
> My next step was to create a stored procedure which just did a check
> for EXISTS on the @Word. This too returned timeout expired whilst the
> previous statement, (checking the reserved words table), executes with
> no problem.
>
> So I stepped even further back and just did a SELECT * FROM table in a
> completely new ASP. This too returns me Timeout Expired as per the
> previous attempts.
>
> Here is the code for this new page:
>
> ------------------------------------------------------------
> <% Option Explicit
> Dim objConn, objRs
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open Application("Database_ConnectionString")
> Set objRs = objConn.Execute("SELECT * FROM dbo.tbl2WaySessions")
> If Not objRs.EOF Then
> Do While Not objRs.EOF
> Response.Write "<p>" & objRs.Fields(0).Value
> objRs.MoveNext
> Loop
> End If
> objRs.Close
> 'Response.Write "<p>" & objConn.Execute("EXEC usp_IsUniqueSessionID
> '36B4PS3293I00NYQ'").Fields(0).Value
> objConn.Close
> Set objConn = Nothing %>
> ------------------------------------------------------------
>
> Now whilst this again might be inefficient, I am starting to really
> loose my marbles with this problem.
>
> As previously stated, the connection string is being used on 100 other
> pages with no problems.
>
> Again, any help would be most welcomed.
>
> Regards
>
> David M
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

David Morgan wrote:

>
> So I stepped even further back and just did a SELECT * FROM table in a
> completely new ASP. This too returns me Timeout Expired as per the
> previous attempts.

Oh wait. No parameters? And it times out? That obviously can't be due to
parameter sniffing.

>
> Here is the code for this new page:
>
> ------------------------------------------------------------
> <% Option Explicit
> Dim objConn, objRs
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open Application("Database_ConnectionString")
> Set objRs = objConn.Execute("SELECT * FROM dbo.tbl2WaySessions")

You said there are no records in this table, right? And you've checked
sp_who (or better sp_who2) to verify that no blocking is occurring ...

Is the problem table-specific? Does it occur when you select records from
other tables?

Bob Barrows