How To Execute Long Running Queries from Active Server Pages (261211)

MORE INFORMATION

You must set a number of timeout values to allow the execution of the stored procedure to complete. The values you use should be based on the estimated time it should take the stored procedure to run.

Timeout Value	Description
Server.ScriptTimeout = NumSeconds	This value determines how long a script should run before it is terminated.
(Command Object).CommandTimeOut = NumSeconds	This value indicates how long to wait for a command that is running before it terminates.

Additionally, use the following statement to prevent the browser from timing out:

<% response.buffer = true %>
				

The code sets up a persistent HTTP connection by sending a Keep-Alive header to the client. This keeps the TCP/IP connection open between the client and server.

The following code sample demonstrates how to set the timeout values:

<%
Response.Buffer = True
Response.Write "<H1>Please Wait .... </H1>"
Response.Flush

Server.ScriptTimeout = 3600

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=<ServerName>;User ID=<UserName>;Password=<Password>")
Set oRs = Server.CreateObject("ADODB.Recordset")
Set oCmd = Server.CreateObject("ADODB.Command")

oCmd.ActiveConnection = oConn
oCmd.CommandTimeout  = 3600
oCmd.CommandText = "TestWait"
oCmd.CommandType = adCmdStoredProc

Set oRs = oCmd.Execute()

Do While Not oRs.EOF
  Response.Write( "Value From Recordset = "  & oRs(1) & "<BR>")
  oRs.MoveNext	
Loop

oConn.Close 

Set oRs = Nothing
Set oConn = Nothing
%>