Stored procedure nesting level exceeded error message

[RCCRookie]

I'm having a problem with a report that was designed in Crystal XI (11.0.0.1282) using a stored procedure and is running against an SQL 2000 DB. The report has a mind of it's own. The report works fine when first opened, however, once I close the report and attempt to reopen I receive the following error message:

CRYSTAL REPORT VIEWER

Database Connector Error: '42000[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure nesting level exceeded (limit 32).[Database Vendor Code:217]'

At this point it's anyones guess how long before I'll be able to run the report again. Usually I can run it the following morning with the same eventual outcome. I have been attempting to resolve this for weeks. I can't find anything helpful to resolve the issue. I apologoize if my thread is unpolished. I'm relatively new to the database and report writing world. If I left any pertinent information out I will be happy to provide anything needed if it helps lead to the eventual resolution of the problem.

 

[LyleU]

RCC,

While I can't answer your question directly, perhaps these excerpts from an article on SQLServerCentral will start you looking in the right direction.

Lyle
___________________________________________________________
Introduction
When one stored procedure calls another stored procedure (SP) you have what is called stored procedure nesting. I often call such stored procedures a switching yard because we often create one stored procedure that executes 1 of many SP’s based on what parameters are sent in. The nest level increases each time a stored procedure calls another SP. When the nest level exceeds 32 the transaction will stop with an error.

You can also have a stored procedure call itself. This type of nesting is called recursion and you will get similar results as those shown above if you exceed the max nesting level.