Weird Stored Procedure Problem - stopping without any errormessage

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

Re: Weird Stored Procedure Problem - stopping without any errormessage

Hi
Run SQL Server Profiler and see what is going on while you call the SP1



"Christian Peterek" wrote in message
news:du1ehb$9s2$1@svr7.m-online.net...
> 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

Re: Weird Stored Procedure Problem - stopping without any errormessage

Christian Peterek wrote:
> 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

No error message? Nothing times out? What symptoms do you perceive in your
client?

> somewhere in a
> cursor loop,


"cursor loop"? Red flag #1

> which makes SP1 stop, too. No SP throws any error
> message.

My first step would be to try to eliminate the cursor.
Otherwise, use SQL Profiler as Uri suggested.

You may be running into a parameter-sniffing issue:
http://tinyurl.com/h7aa
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp .mspx#EEAA

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

Re: Weird Stored Procedure Problem - stopping without any errormessage

Don't know if this will help, but
I remember 'print' statements causing
odd problems with ADO. The solution
was to ensure there simply weren't any (they
were only for diagnostic purposes anyway).

Re: Weird Stored Procedure Problem - stopping without any errormessage

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" wrote in message
news:du1ehb$9s2$1@svr7.m-online.net...
> 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

Re: Weird Stored Procedure Problem - stopping without any errormessage

Just a shot in the dark, but could the stored procedure be timing out? How
long does it take before the procedure stops running?


"Christian Peterek" wrote in message
news:du1ehb$9s2$1@svr7.m-online.net...
> 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

Re: Weird Stored Procedure Problem - stopping without any errormessage

The SPs are fast - in the Query Analyzer it takes about 1 second to finish.

Jim Underwood wrote:
> Just a shot in the dark, but could the stored procedure be timing out? How
> long does it take before the procedure stops running?
>
>

PROBLEM SOLVED - Weird Stored Procedure Problem - stopping withoutany errormessage

It works now.

I add the SET NOCOUNT ON line to the stored procedures, removed all the
print statements and the problem was solved.

Thanks everyone for helping.

Re: Weird Stored Procedure Problem - stopping without any errormessage

The NOCOUNT ON thing helped to solve the problem. Thanks for the tip.


But I don't know what you mean with "Set-based processing".

It would be nice, if you could shortly explain the issue, or post a link
to an article that explains what you mean.

Thanks in advance.


Dan Guzman wrote:
> 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.
>

PROBLEM SOLVED - Weird Stored Procedure Problem - stopping withoutany errormessage

see -> microsoft.public.inetserver.asp.db

Christian Peterek wrote:
> 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

SOLVED Weird Stored Procedure Problem - stopping without any errormessage

see -> microsoft.public.inetserver.asp.db

Christian Peterek wrote:
> 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