ADODB CommandTimeout executing stored procedure in classic asp

Question

I have a problem in a very old project. There is a timeout exception during a long database process.

Here is initial code

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = Common_CnxStr
objCmd.CommandText = "Db.SP_Name"
objCmd.CommandType = adCmdStoredProc
/*some query parameters are added here*/  
objCmd.Execute

This runs for 90 seconds then fails with timeout exception. How can I increase the timeout to, for example, 5 minutes.

I tried to extend the timeout by adding to my code the line objCmd.CommandTimeout

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandTimeout = 300 
objCmd.ActiveConnection = Common_CnxStr
objCmd.CommandText = "Db.SP_Name"
objCmd.CommandType = adCmdStoredProc
/*some query parameters are added here*/  
objCmd.Execute

But this does not work and I still have an exception after 90 seconds. I even tried to do like this

Server.ScriptTimeout = 300
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandTimeout = 300 
objCmd.ActiveConnection = Common_CnxStr
objCmd.ActiveConnection.CommandTimeout = 300
objCmd.CommandText = "Db.SP_Name"
objCmd.CommandType = adCmdStoredProc
/*some query parameters are added here*/  
objCmd.Execute

But this did not change anything. Please help.

Answer 1

try to add this at the beginning of your ASP code:

<%
  Server.ScriptTimeout = 300 ' 5 minutes!
%>

Anyway, if it is a performance issue, you should get the DBA to check the DB instead

See also: https://technet.microsoft.com/en-us/library/bb632464.aspx