Stored Proc returns everything once and then nothing

Databases
1

SQL Server 2000

So I have a SPROC that collects a bunch of data, dumps it into a temporary table and then selects everything for further processing via my web page.

This is supposed to run once a day to send out e-mail notifications.

The problem: The results are somewhat reluctant. I mean, when I run the SPROC once (to test & debug), I get, say, 800 records returned.

Then, a few minutes later, I make some changes on the web page – not touching the SQL – and I run the same SPROC, but this time I don’t get any results.

After that, I go in manually and run the SPROC, stopping execution after each logical section…and I keep getting results.

Generally speaking, does that sound like anything you’ve ever run into? I could post a few hundred lines of code, if that helps, but I was just wondering whether anyone might have some ideas.

Thanks.

PS: The web site I’m running is in Classic ASP, and the specific page simply calls the SPROC, processes the results and then shuts down. It usually works, but every now and then I just don’t get anything back from the db.

2

It almost sounds like the stored proc does some sort of time based querying or it does recursive calls which are processing faster than it can keep up with.

I think there needs to be some more detail before we can help you determine whether it’s an asp problem, a sql problem or just a timeout/connectivity issue.

We’ll need to see at least the stored proc, but the asp code would probably help as well.

Please be sure to at least wrap them in the code tags, if not using the highlight bbcode for each.