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

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"


.

Relevant Pages

  • Issue with retrieving large data over web using Stored Procedure
    ... 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 ... but one sp that retrieves large amount of data does not ...
    (microsoft.public.inetserver.asp.db)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... 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 ...
    (microsoft.public.inetserver.asp.db)
  • Dynamic query to store record count in variable????
    ... I am attempting to write a stored procedure to retrieve the record count but ... EXECUTE sp_executesql @sql, @tablea_count OUTPUT ...
    (microsoft.public.sqlserver.server)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)