[quoted text, click to view]

!??!?
Don't use ASP for this. Why tie up a thread on your web server for that
long??

I'm willing to be that this entire task could be done within a scheduled
stored procedure.


[quoted text, click to view]

Probably ScriptTimeout


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I am having a problem executing long running queries from an ASP application
which connects to SQL Server 2000. Basically, I have batches of queries that
are run using ADO in a loop written in VBScript. This works pretty well
until the execution time of a single query starts to exceed some threshold,
which I am trying to narrow down. I can typically run 2 - 10 queries in a
loop, with the run time being anywhere from under a minute to an hour or
more. Now that this application is being subjected to run against some large
databases (25 - 40G), I'm having problems getting the application to
continue beyond the first query if it takes a while to run.

I used SQL Profiler to try to diagnose what was going on. I can see the
query executes to completion, but immediately after completing I can see an
"Audit Logout" message, which apparently means that the client has
disconnected. The query durations vary from 45 or 50 minutes to up to over
90 minutes. I have the ADO connection and query timeouts set to very large
values, e.g. 1000 minutes, so I can't think its that. My guess is that there
is some IIS setting or timeout that I am running up against and the
connection to SQL Server is just dropped for some reason.

The configuration is

NT 4.0 SP6
SQL Server 2000 SP3
IIS 4.0
Internet Explorer 5.5

I'm only running into this problem on the very largest databases we run
against. The vast majority continue to function properly, but this is going
to happen more often as time goes on the databases continue to grow in size.

Any advice is appreciated,

-Gary

[quoted text, click to view]

I have no choice at the moment. I realize it's not an optimal solution. Also
there are rarely more than a couple requests at a time going on.

[quoted text, click to view]

Some of it is scheduled, but it's designed to work on-demand as well.

[quoted text, click to view]

This is already set to a really large value. (way more than an hour) But
thanks for the suggestions.

-Gary

[quoted text, click to view]

You can kick off a scheduled job that runs a stored procedure using
sp_start_job. To pass parameter values to the procedure, you can insert them
into a control table where they can be read by the stored procedure.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

Gary,

If your ASP times out, you definitely want the connection to stop since
there's no reason for it run anymore :)

In any event, there is an IIS timeout setting, but you can change it in your
script:

<%
Server.ScriptTimeout = Number_Of_Seconds
%>

Craig