CODEWITHSUNDEEP
sqlsql-servervbscript
rrhandle
rrhandle

Reputation: 41

Query timeout expired 80040E31

This vbs script has been running every night for a couple of years. Now I am getting a timeout error. It times out in a matter of seconds.

DataSource = "127.0.0.1"
DatabaseName = "xxxxxxxx"
DBUser = "xx"
DBPassword = "xxxxxx"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This is the original SQL statement
    SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' I tried to remove one of the '0's but it still times out.

SQL = "SELECT top 100000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Interesting that this query runs fine.  It takes a good 10 minutes to run
SQL = "SELECT top 100000 TimeStamp,LogicName, PointValue  from Trends
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim objConnection,objRecordset,strSearchCriteria
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

    objConnection.Open "Provider=sqloledb;Data Source=" & Datasource & ";Initial Catalog=" & DatabaseName & ";User Id=" & DBUser & ";Password=" & DBPassword 

    'MsgBox "Connected"

    objRecordset.Open SQL, objConnection, adOpenStatic, adLockOptimistic

Upvotes: 2

Views: 4920

Answers (3)

Yair Yaya Tendler
Yair Yaya Tendler

Reputation: 159

There are two values you need to change in the ADO string you to extend your ADO connection.

A) set CommandTimeout = 300 B) Set ConnectionTimeout = 300

also, you might want to set ARITHABORT ON if it's a big query it will run much faster.
Code sample below

<%
    Server.ScriptTimeout=600
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = Your-DB-Name; User Id = sa; Password=Your-Passoword"
    conn.ConnectionTimeout = 300
    conn.CommandTimeout = 300
    conn.open
    
    set rs= Server.CreateObject("ADODB.Recordset")
    rs.locktype=3
    rs.cursortype=2
    
    SQL="set ARITHABORT ON select * from table" 
    
    rs.Open SQL, conn
    while not rs.eof 
    response.write(rs(0))
    rs.movenext
    wend
    
    rs.close
    conn.close
%>

You can also use the field/colum name in the rs connection example response.write(rs("FirstName"))

Upvotes: 2

rrhandle
rrhandle

Reputation: 41

The problem was solved by not asking for 1000000 records. Instead, I changed his script to grab all records between the desired dates.

Upvotes: 1

Chuck
Chuck

Reputation: 1031

In your connection string, it defaults to 30 seconds, increase the timeout to 900 seconds at least.

Upvotes: 0

Related Questions