SP says it's exceeded nesting level when there is no nesting involved!

Question

Post reply

SP says it's exceeded nesting level when there is no nesting involved!

chrismat

SSChasing Mays

Points: 606
More actions
April 1, 2010 at 1:19 pm

#156835

In one database (DB2RWDATA) is an existing (and working) SP that I need moved to a new database; I used Script Stored Procedure As and CREATE To New Query window. In front of each table reference I added DB2RWDATA.dbo to reference tables in the first database, and ran that script in the new database.
Running the SP from the new database returns the correct data, but it keeps running until I get:
Msg 217, Level 16, State 1, Procedure spAchDetails, Line 55
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
The Results tab shows 32 separate result sets all with the same data, and the messages tab keeps "(1 row(s) affected)" several dozen times (interestingly it's a lot more than the number of result sets).
The SP doesn't call any other SPs (and certainly not itself); the only functions used are built in functions (STUFF, RIGHT, RTRIM, and ISNULL). It still works fine in the original location. I also tried deleting the original SP in DB2RWDATA and ran the same CREATE PROCEDURE script in the original DB, so now it has the DB2RWDATA.dbo in front of all the tables; running the new version of the SP from DB2RWDATA shows one resultset as expected...
How can I go about figuring out why the new database is treating it as nested when it's really not?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Post your proc here. we can't debug what we can't see. 😀

AND

Read this ...

For better, quicker answers on T-SQL questions, click on the following...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Is there a trigger on the table in the new database that might be recursively calling itself based on data being manipulated?

And then again, I might be wrong ...
David Webb

chrismat SSChasing Mays Points: 606 More actions · Answer 3

I deleted the SP from the new db and recreated it; now it's working.

In order to test, I added "exec sp" to the end of the query window... I think what happened was when I created the SP in the new location that I didn't highlight just the CREATE PROCEDURE part, so the entire script including the exec became part of the SP... Apparently the entire query window becomes the SP and doesn't stop at the END; statement at the end of the CREATE PROCEDURE... I didn't realize it would do that. Learned something new!

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Yep, the batch continues on until it hits a 'GO' statement

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

chrismat (4/1/2010)
I deleted the SP from the new db and recreated it; now it's working.
In order to test, I added "exec sp" to the end of the query window... I think what happened was when I created the SP in the new location that I didn't highlight just the CREATE PROCEDURE part, so the entire script including the exec became part of the SP... Apparently the entire query window becomes the SP and doesn't stop at the END; statement at the end of the CREATE PROCEDURE... I didn't realize it would do that. Learned something new!

Congrats on finding the issue.

This would be a nice example of a bad loop.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 6

CirquedeSQLeil (4/1/2010)
Congrats on finding the issue.
This would be a nice example of a bad loop.

Bad loop indeed. Hmm, what would be a good loop? :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

WayneS (4/1/2010)
CirquedeSQLeil (4/1/2010)
Congrats on finding the issue.
This would be a nice example of a bad loop.
Bad loop indeed. Hmm, what would be a good loop? :w00t:

A hoola hoop.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

WayneS (4/1/2010)
CirquedeSQLeil (4/1/2010)
Congrats on finding the issue.
This would be a nice example of a bad loop.
Bad loop indeed. Hmm, what would be a good loop? :w00t:

Recursion to the fridg for the next beer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 9

chrismat (4/1/2010)[hrApparently the entire query window becomes the SP and doesn't stop at the END; statement at the end of the CREATE PROCEDURE... I didn't realize it would do that. Learned something new!

This is something I have seen a lot. The number of procedures that have GRANT statements tacked on the end...:laugh: The key, as Jason says, is to get in the habit of appending a batch terminator to your CREATE/ALTER statements.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Paul White NZ (4/2/2010)
chrismat (4/1/2010)[hrApparently the entire query window becomes the SP and doesn't stop at the END; statement at the end of the CREATE PROCEDURE... I didn't realize it would do that. Learned something new!
This is something I have seen a lot. The number of procedures that have GRANT statements tacked on the end...:laugh: The key, as Jason says, is to get in the habit of appending a batch terminator to your CREATE/ALTER statements.

Coincidentally, I have seen this occur a lot too (GRANT)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 11

I caught trigger definitions in one once 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi