Re: Weird Stored Procedure Problem - stopping without any errormessage
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Feb 2006 06:47:20 -0600
My first guess would be that you don't have SET NOCOUNT ON in all of your
procs. If you haven't already done so, add it to the beginning of all of
the proc to see of that fixes the problem. SET NOCOUNT ON will suppress
DONE_IN_PROC messages that are returned to ADO apps as empty/closed
recordsets and can cause problems if your application isn't expecting them.
Also, consider revisiting the SQL code to eliminate the cursor(s).
Set-based processing usually performs much better than cursors. Cursors are
seldom required and often used only because the developer is more familiar
with procedural processing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Christian Peterek" <spam_den_chris@xxxxxx> wrote in message
news:du1ehb$9s2$1@xxxxxxxxxxxxxxxxxxxx
Hi NG,
Currently I am encountering a weird problem, which even the big google
could not fix.
I've got a stored procedure(SP), that calls two other stored procedures.
SP1 (which i call from the classic asp) calls first the SP2 and then the
SP3.
But for some reason, the SP3 just aborts it's work somewhere in a cursor
loop, which makes SP1 stop, too. No SP throws any error message.
The weird thing is, that if I call the SP1 from the MS Query Analyzer
(with the same Parameters as from the ASP script), it works as it should.
I scrolled through the Messages window, and there is no error message,
too. So I assume, that the SPs are correct.
I added a "debug print" to the SP1 and SP3, that just inserted lines into
a table. I added these lines to the both while loops in the SP3 and every
time I ran it from asp, the loops stopped at another position (somewhere
close to each other but still it was always another position-count that
was "printed" to the table), but the parameters, and the work the SP had
to perform did not change.
As I multiplied the debug print ( had the SP3 to write 6 times per loop to
the log table) the SP3 aborted even earlier, when called from asp. But not
from the Query Analyzer.
*I even tried different ways to use the ADODB.Connection object, but the
effect was always the same.
*I can rule out that the problem is connected to authentication
difficulties or something like that.
*I tried to call the SP1 with and without the transactions.
*I'm sure that the SPs are all working and correct, throwing no errors.
*Running the SP1 from the Query Analyzer takes about 1 second. So its not
a Timeout problem.
*Calling SP3 from ASP alone works.
So are the questions:
* Is there any Query / Statement limit?
* Can I do anything to solve the problem?
* Any other Ideas?
Thanks for your help,
Chris
X-POST:microsoft.public.sqlserver.programming
X-POST:microsoft.public.scripting.vbscript
X-POST:microsoft.public.inetserver.asp.db
FOLLOWUP-TO:microsoft.public.inetserver.asp.db
.
- Follow-Ups:
- Re: Weird Stored Procedure Problem - stopping without any errormessage
- From: Christian Peterek
- Re: Weird Stored Procedure Problem - stopping without any errormessage
- Prev by Date: Re: Weird Stored Procedure Problem - stopping without any errormessage
- Next by Date: Re: Weird Stored Procedure Problem - stopping without any errormessage
- Previous by thread: Re: Weird Stored Procedure Problem - stopping without any errormessage
- Next by thread: Re: Weird Stored Procedure Problem - stopping without any errormessage
- Index(es):
Relevant Pages
|
