none
Microsoft OLE DB Provider for SQL Server error '80040e31' - Timeout expired

    Question

  • Hi

    I have an ASP page that runs a stored procedure to pull data back from the SQL 2005 server. The query runs fine in SQL Query Analyser and takes approx 45 seconds.
    At exactly 30 seconds i get the 80040e31 - Timeout Expired.

    I have set the CommandTimeout value at 60000

    "conn.commandtimeout=60000"

    Still the connection times-out after 30 seconds. The query does work as it has it works occasionally but not very often. The problem appears to be that something is ending the communication.

    Thanks

    Paul
    Friday, May 15, 2009 2:32 PM

Answers

  • Hi Paul,
      I believe the CommandTimeout is a property of the command, rather than the connection. Try this and see if it helps:

    command.CommandTimeout = 60000

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, May 15, 2009 5:46 PM
    Moderator

All replies

  • Hi Paul,
      I believe the CommandTimeout is a property of the command, rather than the connection. Try this and see if it helps:

    command.CommandTimeout = 60000

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, May 15, 2009 5:46 PM
    Moderator
  • Hi Dan

    Can you expand what you mean a little? Here is my connection string, I tried to add in
    command.CommandTimeout = 60000
    But the object "command" doesn't exist
    _________________________________________________________________________
         MM_dataconn_STRING = "Provider=SQLOLEDB;" & _
            "Data Source=111.222.333.444;" & _
            "Initial Catalog=databasename;" & _
            "Network=DBMSSOCN;" & _
            "User Id=username;" & _
            "Password=password"    

        set conn = CreateObject("ADODB.Connection")
        conn.connectiontimeout=30000
        conn.CommandTimeout=60000
        command.CommandTimeout = 60000 <---- added this in but it's not defined so it fails
        Server.ScriptTimeout = 60000
        conn.open MM_dataconn_STRING

    Function strGetConnectString()
        strGetConnectString = MM_dataconn_STRING
    End Function
    _________________________________________________________________________

    Thanks

    Paul
    Monday, May 18, 2009 1:23 PM
  • Where are you actually doing the execution of the statement you want to fire against the SQL Server. In the portion you mentioned, only the connection is opend.

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Monday, May 18, 2009 4:56 PM
    Moderator
  • Is this what you mean?

    Dim RS_view
    Dim RS_view_numRows

    Set RS_cc_view = Server.CreateObject("ADODB.Recordset")
    RS_view.ActiveConnection = MM_dataconn_STRING
    RS_view.Source = "SELECT TOP 10 *  FROM dbo.View_myview"
    RS_view.CursorType = 0
    RS_view.CursorLocation = 2
    RS_view.LockType = 1
    RS_view.Open()

    RS_view_numRows = 0
    Tuesday, May 19, 2009 1:05 PM