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

Thanks for reply.

Are you referring to the Database Engine Tuning Advisor?
If it is, how do I go about analyzing a stored procedure with selecting
multiple tables?
I am trying to figure out where the Query Tuning tool is located.

Thanks.

"Bob Barrows [MVP]" wrote:

OK, I repeat: is there any chance of optimizing this stored procedure? SQL
2005 has a great Query Tuning tool that may make some useful
recommendations. Give it a try.

You should ask for assistance with optimizing the procedure at the
..sqlserver.programming newsgroup.

Justin Doh wrote:
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"

--
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
    ... It appears that it was hitting the stored procedure, ... Is there any tool in the SQL Profiler that analyze each Trace? ... The maximum amount of time for a script to execute was exceeded. ... do it at ASP. ...
    (microsoft.public.inetserver.asp.db)
  • 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: Insert using an Oracle stored procedure
    ... I sent another poster some info about how we code SP calls in asp (you need more than just the Sql you have, ... >8.1.7 database using the stored procedure below: ...
    (microsoft.public.inetserver.asp.db)
  • Re: DatePart VBScript & SQL
    ... I'm pretty new to the SQL side of ASP. ... > Does that make any sense or do I sound like an idiot... ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)