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]]

Re: ADO client disconnects after running a long query

Gary wrote:[color=blue]
> 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.[/color]

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

[color=blue]
> 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[/color]

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]

Re: ADO client disconnects after running a long query

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:udDZnd$fEH A.1972@TK2MSFTN GP09.phx.gbl...[color=blue]
> Don't use ASP for this. Why tie up a thread on your web server for that
> long??
>[/color]

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.
[color=blue]
> I'm willing to be that this entire task could be done within a scheduled
> stored procedure.[/color]

Some of it is scheduled, but it's designed to work on-demand as well.
[color=blue]
>[color=green]
> > 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[/color]
>
> Probably ScriptTimeout[/color]

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

-Gary

[Craig Kelly]

Re: ADO client disconnects after running a long query

"Gary" wrote:[color=blue]
> 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.[/color]

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.ScriptTi meout = Number_Of_Secon ds
%>

Craig

[Bob Barrows [MVP]]

Re: ADO client disconnects after running a long query

Gary wrote:[color=blue]
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:udDZnd$fEH A.1972@TK2MSFTN GP09.phx.gbl...[color=green]
>> Don't use ASP for this. Why tie up a thread on your web server for
>> that long??
>>[/color]
>
> 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.
>[color=green]
>> I'm willing to be that this entire task could be done within a
>> scheduled stored procedure.[/color]
>
> Some of it is scheduled, but it's designed to work on-demand as well.[/color]

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.