Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

Recently, In our company website We had a tremendous amount of database grow. Due this huge growth of the database we encountered timeouts error on the website.

This is the error that we continually received:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
updatedata.asp, line 19

I searched in google search engine as well as in yahoo. I found alot of ideas to fix this error. Few suggested to increase the script timeout setting in IIS. But we hosted our website in shared server. So I dropped this idea and tried to find out is there any other soluton for this error.

Then I changed my strategy to search for solutions what I can do in ASP coding. Atlast I found out the solution for this error. I found out that there were two specific settings that would affect an ADO script timeout. Using the Connection Object for ADO, I saw Command Timeout and Connection Timeout. I manipulated my code to look like this:

dim AConn


Set AConn = Server.CreateObject("ADODB.Connection

AConn.CommandTimeout = 0

AConn.Open "Provider=MSDASQL;Driver={SQL Server};Server=server;Database=database;UID=uid;PWD=pwd;"

The line AConn.CommandTimeout = 0 sets the CommandTimeout strictly for ADO. Setting it to 180 would be three minutes. Setting it to 0 tells it to work to infinity.

I then created my recordset object:

mySQL = "Select * from tblTableName
SET adoRS = Server.CreateObject("ADODB.Recordset")
adoRS.Open str_Select, myConn, adOpenKeyset, adLockOptimistic

Finally this setting worked out perfectly and quickly. I hope this one will help others in future.