November 29, 2010 at 10:56 am
I have the following page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Time Data</title>
</head>
<body>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionTimeout = 120
oConn.Open "Provider=SQLOLEDB; Server=xxxxx; Database=MDR; Uid=xxxxx; Pwd=xxxxx"
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT TOP 1 AVG(Zero) FROM mCallEnd WHERE (CallKind=0 OR CallKind=1 OR CallKind=16) AND Zero>0 AND OrgAccount=5127066669 AND Timestamp >= DATEDIFF(n, '12-31-1899', DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))"
Set rs = oConn.execute(SQL)
do while NOT rs.EOF
if rs.recordcount = 0 then
Response.Write "No records returned"
else
Dim Zero_Sec
Zero_Sec = rs("Zero")
Response.Write "Average “Zero” field since midnight: " & Zero_Sec %><br /> <%
rs.MoveNext
end if
Loop
rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing
Set oConn2 = Server.CreateObject("ADODB.Connection")
oConn2.Open "Provider=SQLOLEDB; Server=xxxxxx; Database=MDR; Uid=xxxxx Pwd=xxxxx3"
Dim rs2
Set rs2 = Server.CreateObject("ADODB.Recordset")
SQL2 = "SELECT TOP 1 Zero FROM mCallEnd WHERE (CallKind=0 OR CallKind=1 OR CallKind=16) AND OrgAccount=5127066669 ORDER BY Timestamp DESC"
Set rs2 = oConn2.execute(SQL2)
do while NOT rs2.EOF
if rs2.recordcount = 0 then
Response.Write "No records returned"
else
Zero = rs2("Zero")
Response.Write "The actual Zero amount for the last account entry is: " & Zero %><br /> <%
rs2.MoveNext
end if
Loop
rs2.Close
Set rs2 = Nothing
oConn2.Close
Set oConn2 = Nothing
%>
and when I try to go to it, I receive the error 80040e31, I've reviewed several web pages pertaining to this and they all seem to talk about the connection string as being something that can cause this issue or that it could be a permissions issue. I can verify that this is not a permissions issue as we have several queries that use the same server that is in this query and they work fine.
We have this page:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Time Data</title>
</head>
<body>
<%
Set oConn2 = Server.CreateObject("ADODB.Connection")
oConn2.Open "Provider=SQLOLEDB; Server=10.2.1.41; Database=MDR; Uid=xxxxx; Pwd=xxxxx"
Dim rs2
Set rs2 = Server.CreateObject("ADODB.Recordset")
SQL2 = "SELECT TOP 1 Zero FROM mCallEnd WHERE (CallKind=0 OR CallKind=1 OR CallKind=16) AND OrgAccount=5127066669 ORDER BY Timestamp DESC"
Set rs2 = oConn2.execute(SQL2)
do while NOT rs2.EOF
if rs2.recordcount = 0 then
Response.Write "No records returned"
else
Zero = rs2("Zero")
Response.Write "The actual Zero amount for the last account entry is: " & Zero %><br /> <%
rs2.MoveNext
end if
Loop
rs2.Close
Set rs2 = Nothing
oConn2.Close
Set oConn2 = Nothing
%>
</body>
</html>
which produces a result.Can anyone assist with this?
Thank you
Doug
November 29, 2010 at 11:08 am
#1 - Make sure you can connect to the server with that username/password
#2 - Make sure you can ping SQL Server from the web server
#3 - Check out http://www.connectionstring.coms to see alternative connection strings to try.
November 29, 2010 at 12:11 pm
Ninja,
The server is pingable from the SQL server and both pages use the same username/pwd combination which works. What I'm not sure of is why another connection string would be necessary in this connection string, if we can verify that on the second page that I sent, the connection string works. Which is the same string for both pages.
November 30, 2010 at 8:51 am
I just ran the query in question from the server itself and it took me almost 2:45, but the other person who brought this to my attention, said that he increased the timeout in the web page to 3 minutes, and the same problem occurred. I know for a fact that changed the timeout from 120 to 3600 seconds and I had the same error occur.
November 30, 2010 at 9:01 am
How long does it take to run the query from query analyser?
Sounds more like the problem is the query rather than the connection itself.
I was under the impression that you were never able to connect to the server, hence a new connection string method could have worked.
November 30, 2010 at 10:03 am
Ninja,
Yes we were able to connect to the server via the page. In my testing here's what I've done so far:
1. Increased the time out in the page itself from 120 seconds to 3600 seconds.
2. Ran the page without the query, and the page loads fine.
3. Ran the query on the sql server itself in query analyzer. It took 2:43.
So my conclusions are that it's not the query but something else. I know that it's not permissions on the sql server, since I have another page with a a query to the same sql server that doesnt time out. So my question is, what else could it be?
November 30, 2010 at 10:11 am
Well you say the query takes almost 3 minutes to run in QA and that the page fails around the same time.
Permission issue should be found long before that (assuming simple queries).
What happens if you change the query to return only 1 row manually (either TOP 1 or SELECT 1 as cola, 'blah' as col2....).
If the query runs in less than 1 sec and the page never fails to load then the problem is query taking too long.
You need to make sure it runs faster than that.
Last resort would be to change the timeout on IIS page process and adodb.command.
November 30, 2010 at 10:20 am
Maybe this can help too. But I would definitely tune the query first.
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040e31-errors.html
In my experience anything above 2 seconds to load on the net is considered crawling... and I see no reason why that query should take longer than 1 second to run.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply