Re: Issue with retrieving large data over web using Stored Procedu

It appears that it was hitting the stored procedure, and then it gives this
stats.

CPU: 145569
Reads 76727637
Writes 125593
Duration 214490

- Is there any tool in the SQL Profiler that analyze each Trace?

On the web, it stills give the error message.

"Active Server Pages error 'ASP 0113'

Script timed out

The maximum amount of time for a script to execute was exceeded. You can
change this limit by specifying a new value for the property
Server.ScriptTimeout or by changing the value in the IIS administration
tools."

- I do not have an authority to change any setting at the IIS, so I need to
do it at ASP.

Thanks.

"Bob Barrows [MVP]" wrote:

You don't use it to run the stored procedure. You use it to trace commands
being run by sql server while your asp page is attempting to run the stored
procedure.

In other words, start sql profiler, create a new trace and start it, open
your browser and navigate to the asp page that is giving you the problem.
Look at the trace to see if you asp page successfully connected to the
server and issued the command to execute the stored procedure.

Justin Doh wrote:
Hi Bob,

Wow, I did not know there was SQL Profiler at SQL 2005.
How do I use SQL Profiler at SQL 2005 to run the stored procedure?

Thanks in advance.
Justin

"Bob Barrows [MVP]" wrote:

You are attempting to call it from an ASP page, correct? You need to
verify that when you run the ASP page, that the procedure is
actually getting called. The best way to do that is via SQL Profiler.

How do I use "on error resume next"?
???
I meant for you to look in your vbscript code in your .asp file and
verify if an "on error resume next" statement exists in your code.
If it does, comment it out so you can see any errors that occur.

Justin Doh wrote:
Actually the stored procedure works fine because it retrieve data
while I execute the sp at the SQL Server 2005.
I don't think I need to check at the SQL Profiler because I am
testing at the SQL 2005 level.
How do I use "on error resume next"?

Thanks in advance.

"Bob Barrows [MVP]" wrote:

Justin Doh wrote:
Hello.
I moved my stored procedure and tables from SQL 2000 to SQL 2005.
I am having an issue to retrieve a large amount of data using
previous stored procedure (sp) over web (ASP page).
Other sp works fine, but one sp that retrieves large amount of
data does not even get executed over ASP page.

Have you verified this with SQL Profiler? If not, you should.


First,
I tried to increase the server timeout by doing this way.
<% server.ScriptTimeout = 40000 %>

Second,
I also included ConnectionTimeout and CommandTimeout to see if it
would help any such as..

With rsReport
.ConnectionString = ConnOLAP
.ConnectionTimeout = 4800

This one is irrelevant to this issue

.CommandTimeout = 4800

4800 is ridiculous, especially if the procedure is not getting
executed.

.Load(strSQL)
End With

When I executed sp at SQL Server 2005 level, I got the data fine
(after long period of process), but when it gets executed over web
(ASP), no data gets retrieved.

Is there any suggestions to fix this problem?


If you have "on error resume next" anywhere, comment it out so you
will see errors.

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

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

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



.

Relevant Pages

  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Scheduled DTS package doesnt run
    ... I created a DTS package wich runs a standard Stored Procedure in the master ... I call the stored procedure by an Execute SQL Task object with all necesarry ... When I log on as administrator on the server and run the package manually it ... Error string: SQL Web Assistant: Could not execute the SQL statement. ...
    (microsoft.public.sqlserver.dts)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... other improvements I could do at the ASP page? ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: ASP, looping, and stored procedures.... error 800a0bb9 ...
    ... I'm just going to include the .asp page here. ... > If your bottleneck turns out to be occurring in the ASP code itself rather ... > stored procedure, then you may want to think about utilizing GetRows ... >> Oh, and by the way, the test box has SQL server and IIS on it. ...
    (microsoft.public.inetserver.asp.db)
  • Re: user permissions
    ... I'm not sure how that would work with the security as the kill sql command is ... I would like to add some comments regarding 'EXECUTE AS' clause in SQL ... this issue by creating a stored procedure with 'EXECUTE AS' clause in SQL ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.security)