I have a stored procedure executing on a SQL 2k database that receives as
an Input; the database to query and the limits for the query. This stored
procedure is executed from ASP running on ISS located on a different server.
From time to time the ASP code seems to hang on processing of the SQL.
From tests, I know that the entire recordset has not been passed to my recordset
object and it appears that I am waiting for additional data from the SQL
server. Whenever I run the SQL in query analyzer it all seems to work great.
Most of the time I have no problems. I've set the SQL server and Web server
on the same box to eliminate network activity as a concern; the problem still
occrus after some period of time. I am unable to recreate the error on a
consistant basis; but it manages to show up on it's own after a period of
time. (I thoght it might have something to do with scheduled tasks, but
I shut all those off, and the problem still appeared.) Keep in mind that
this has hundreds of users interacting with the system at any given time.
So it is very difficult to isolate the problem down to 1 or 2 users or queries,
or parameters being passed in to the Stored Procedure

As a last ditch effort, I force the SQL server to IGNORE any caching that
the SP has in an effort to see if there was a problem w/ the execution plan
or the Caching of the SP after first run. Turned out that this seems to
have eliminated the problem. What I don't understand is
1) Why this would work
2) Is there any way to turn this around so that I can get the benefit of
my precompiled sp, instead of having to force recomplile every time?

Sites that explain this unique problem would be appreciated.

I have a few suspicions as to Why my solution works; but I want to see if
others have the same ides. My explination forces me to always recompile
the SP; so I'm looking for something better.