Re: Nesting Level - What should I expect when executing after prep

Thanks for explaining the "why" - what I really need to know though is if
there is a way to identify calls made this way. The nesting level restriction
is embedded in the design for our application for a number of reasons; we use
stored procedures that we've configured as "external" procedures to set up a
common execution environment, error handling method, and logging. The
procedures (again - long explanation) have a nestlevel and open transaction
test. We can certainly configure an exception for procedures called with one
of the sp_* procedures, but we need to be able to identify them. Do you know
of any way?

Thanks,
Kimber


Kimberly Blum wrote:

Hi,

For various reasons (insert long explanation here), we have a check set up
inside of some of our stored procedures to check for @@Nestlevel > 1. This
works fine in most cases, both from SMS and with non-MS drivers, with
@@Nestlevel returning 1. However, when one of our clients began using the sql
server jdbc driver @@Nestlevel was greater than 1. Can anyone outline for me
what happens at the db level when a stored procedure executed from an object
created with prepareCall? What @@NestLevel can I expect? And is there any
way to identify cases of calls sent this way?

Hi. This is probably because the JDBC driver (when you use PreparedStatements
or CallableStatements) executes your SQL/procedure via another more generic
stored procedure, like sp_exec() or something like that.
The DBMS has the notion of nest level to set a simple hard limit(32) to the
depth to which stored procedures can call stored procedure. This protects
against infinite recursion.
However, it is unlikely that you are treading anywhere close to the
limit, so for the most part, it *should* be of no concern. Any stored
procedure which checks nest level will have to explain why it does so
via it's logic.

Joe Weinstein at BEA Systems


.

Relevant Pages

  • Re: Nesting Level - What should I expect when executing after prepareC
    ... For various reasons (insert long explanation here), we have a check set up inside of some of our stored procedures to check for @@Nestlevel> 1. ... stored procedure, like sp_execor something like that. ... The DBMS has the notion of nest level to set a simple hard limitto the ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: chooses not to generate code at all
    ... >>>DBMS is so that the DBMS engine can execute it when triggered by some ... If there is no trigger, ... Stored procedure is one thing. ... > If the procedure execution is not triggered by DBMS ...
    (comp.object)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL DBA Consultants
    ... When I mentioned "loops" it's just ... > because that's the way it appears if you trace the execution. ... > SQL statement is constructed. ... >> sql within a stored procedure I am guessing that you are building and ...
    (microsoft.public.sqlserver.programming)
  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... stored procedure at the subscriber will have. ... replication to the subscriber and the possibility of moving the ... > obtains will be released at the end of its execution. ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading