ADO client disconnects after running a long query

[Gary]

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

[Bob Barrows [MVP]]

Gary wrote:

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.
!??!?
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.

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

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"

[Gary]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ud**************@TK2MSFTNGP09.phx.gbl...

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

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.
I'm willing to be that this entire task could be done within a scheduled
stored procedure.
Some of it is scheduled, but it's designed to work on-demand as well.

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

Probably ScriptTimeout

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

-Gary

[Craig Kelly]

"Gary" wrote:

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.

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

[Bob Barrows [MVP]]

Gary wrote:

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ud**************@TK2MSFTNGP09.phx.gbl...

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

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.

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

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

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.