SQL Server error '80040e31'

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dougancil
    Contributor
    • Apr 2010
    • 347
    #1

    SQL Server error '80040e31'

    I have the following page

    Code:
    <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. Can someone please take a look at my connection string and see if this is what may be causing my issue with this page?

    Thank you,
    Doug
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      It could be a million things, including an invalid username and password, your sql server not allowing the connection, the user does not have the rights to the db, etc...

      ~~ CK

        Comment

        • dougancil
          Contributor
          • Apr 2010
          • 347
          #3
          CK,

          this page:

          Code:
          <!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=xxxxxx"

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>
          produces a result on the same server, so I know it's not a permission issue but something wrong with the first query. Does that help in narrowing down a bit?

          Thank you

          Doug

            Comment

            • ck9663
              Recognized Expert Specialist
              • Jun 2007
              • 2878
              #4
              Get query from your first post and put it in your second post and run it. If it fails, your query is your problem, not the connection.

              Good Luck!!!

              ~~ CK

                Comment

                • dougancil
                  Contributor
                  • Apr 2010
                  • 347
                  #5
                  CK,

                  I ran the query on the server itself and it took almost 2:45. I increased the time out in the page itself to 3600 seconds and still had the same issue. I know it's not the query because that works.

                    Comment

                    • ck9663
                      Recognized Expert Specialist
                      • Jun 2007
                      • 2878
                      #6
                      Get the connection string on your second post and use it on your first post and see if it will work...

                      Good Luck!!!

                      ~~ CK

                        Comment

                        • dougancil
                          Contributor
                          • Apr 2010
                          • 347
                          #7
                          I just tried the connection string from the second post and tried it ... still gave me a timeout.

                          here is the string that I tried:

                          Set oConn = Server.CreateOb ject("ADODB.Con nection")

                          oConn.Open "Provider=SQLOL EDB; Server=10.2.1.4 1; Database=MDR; Uid=xxxxx; Pwd=xxxxx

                          here is what it was:

                          Set oConn = Server.CreateOb ject("ADODB.Con nection")
                          oConn.Connectio nTimeout = 120
                          oConn.Open "Provider=SQLOL EDB; Server=10.2.1.4 1; Database=MDR; Uid=xxxxx; Pwd=xxxxx"

                            Comment

                            • ck9663
                              Recognized Expert Specialist
                              • Jun 2007
                              • 2878
                              #8
                              Then it's not your connection string.

                              Is 120 in minutes or seconds? If it's in seconds, that's just 2 minutes...You said your query runs for more than that... You might want to increase it some more...

                              Good Luck!!!

                              ~~ CK

                                Comment

                                • dougancil
                                  Contributor
                                  • Apr 2010
                                  • 347
                                  #9
                                  CK,

                                  I posted this on entry #5:

                                  I increased the time out in the page itself to 3600 seconds and still had the same issue. I know it's not the query because that works.

                                  I believe that it's the query that's causing the issue here, but I have no way of decreasing the amount of time that it's taking to make the calculations that it's doing.

                                    Comment

                                    • ck9663
                                      Recognized Expert Specialist
                                      • Jun 2007
                                      • 2878
                                      #10
                                      Then let's optimize your query.
                                      1. Do you have the proper index?
                                      2. What's this: DATEDIFF(n, '12-31-1899', DATEADD(dd, DATEDIFF(dd,0,g etdate()), 0)) for? As much as possible, avoid using function in your WHERE clause.
                                      3. AVG is an aggregate function, no need to use TOP clause. It will always return a single record. Specially without the GROUP BY clause.

                                      ~~ CK

                                        Comment

                                        • Patrick Lambin
                                          New Member
                                          • Dec 2010
                                          • 2
                                          #11
                                          Hello,

                                          80040e31 is for the timeout during the execution of the command not for the connection.
                                          I am always using .Net Frameworks 3.5 or 4.0 with VB or VC#. I know that there is a commandtimeout. Maybe i am going wrong but it seems to me you are working with VB 6 and the "old" ADO . You should look in your documentation about a possible command timeout

                                          Have a nice day

                                          PostScriptum : Could you check whether there is a command timeout on the level of the server ( something like RemoteQueryTime Out often set to 600 seconds ).
                                          The DBA may have enabled the queries governor which limits the duration od a query.
                                          Last edited by Patrick Lambin; Dec 8 '10, 10:07 PM. Reason: postscriptum added )

                                            Comment

                                            Previous template Next
                                            Working...