ASP page times out, but Query Analyzer processes fine

[XgrinderX]

Hello folks!

I have an issue that is very puzzling. We have a web page that is performing a search in SQL. It is timing out on the line where we open the recordset and pass a call to the stored procedure.

The weird thing is that in Query Analyzer I can run the exact same query and get the results back in 15 seconds or less most of the time. In our ASP page, all our timeouts are set to 180 seconds.

So we cannot figure out why it is taking so long for the SQL server to send the results back to the recordset on the web server. We have several web sites on this web server server that are using this same SQL server and only one of the sites is having this issue.

Does anybody have any insight that may help us resolve this problem?

Thanks,

-Greg

 

[Ayac]

Are you running the exact same query in the analyzer, or you run the stored 'execute command' you use in ASP?

 

[XgrinderX]

In ASP, my line looks like this:

rsResult.Open "exec spProcedure 'parm1','parm2','parm3'"

In Query Analyzer I am running:

exec spProcedure 'parm1','parm2','parm3'

Does that help?

 

[foxbox]

What do you do with rsResult?

a classic error is that you forget rsResult.movenext in the loop...

hth,
Foxbox

 

[XgrinderX]

Foxbox,

As stated in my first post: "It is timing out on the line where we open the recordset and pass a call to the stored procedure." In other words, the line that is causing the timeout is the "rsResult.Open" line. Hehe....trust me, I have made the "forget MoveNext" mistake many times

It is taking too long for the recordset to be built and we cannot figure out why. If it was happening on all our sites we would think it was a network issue. But it's just this one site.

Any other thoughts on this?

 

[foxbox]

Try this:

rsResult.Open "spProcedure 'parm1','parm2','parm3'", Conn


hth,
Foxbox

 

[XgrinderX]

Well, we already have the connection set in a previous line like so:

rsResult.ActiveConnection = Conn

I also will take this opportunity to also explain that this code is not new, it has been running on the server for over a year. It just started acting up yesterday. This is why it is so puzzling to us. I was just hoping that maybe someone else had experienced a similar problem and could give a suggestion on how to sove it.