Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) Expand / Collapse
Author
Message
Posted Thursday, January 22, 2009 11:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 7:24 AM
Points: 283, Visits: 115
I have a Stored Procedure and getting a Error
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)".

Please help.
Post #642288
Posted Friday, January 23, 2009 12:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
It means you're calling a procedure, view or function from your procedure, and then from that proc calling another, etc 32 levels deep. Or it could be recursive with the proc calling itself.

Either way, you're going to have to track down where that is coming from and change the code so that the nesting level isn't anywhere close to that deep. There's no way to change the maximum nesting level.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #642300
Posted Monday, March 23, 2009 10:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 7:24 AM
Points: 283, Visits: 115
In my SP, I'm trying to delete the No. of rows on the basis of some conditions.
So, that query is acting as recurssive. Is there a way to change that option.
Post #682047
Posted Tuesday, March 24, 2009 1:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
shishir999_ril (3/23/2009)
In my SP, I'm trying to delete the No. of rows on the basis of some conditions.
So, that query is acting as recurssive. Is there a way to change that option.


That shouldn't be a recursive query unless you're explicitly calling a function from within itself. Post the query please?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #682109
Posted Tuesday, April 19, 2011 1:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 122, Visits: 9,006
I would put $ on it being recursive because there is a trigger on that table that edits/deletes other records in the table that then fire the trigger, etc. etc.

4x4 photos
Post #1095442
Posted Tuesday, April 19, 2011 1:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
Please note: 2 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1095451
Posted Saturday, May 21, 2011 3:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
Hi,

Looks like my question is related to this thread.

I am trying to delete some rows in parent table and related rows in child tables. I am using below sp, but throwing error. Please help me to update the sp so that I can delete all related tables rows.



error:
Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

CREATE Procedure spDeleteRows
/*
Recursive row delete procedure.

It deletes all rows in the table specified that conform to the criteria selected,
while also deleting any child/grandchild records and so on. This is designed to do the
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
table to find any child tables, then deletes the soon-to-be orphan records from them using
recursive calls to this procedure. Once all child records are gone, the rows are deleted
from the selected table. It is designed at this time to be run at the command line. It could
also be used in code, but the printed output will not be available.
*/
(
@cTableName varchar(50), /* name of the table where rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows required */
@iRowsAffected int OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare @cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@iChildRows int /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
/* build the criteria to delete rows from the child table. As it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
/* call this procedure to delete the child rows */
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName


--delete a row, example
exec spDeleteRows 'X', 'field1 = ''234''', 0
Post #1112939
Posted Saturday, May 21, 2011 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 7,040, Visits: 12,967
It's worth to notice that laddu4700 already posted his question in a different thread.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1112940
Posted Friday, February 17, 2012 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 7:54 AM
Points: 1, Visits: 19
Thanks for this suggestion, I had a similar problem and i was able to resolve it after removing a proc call within the same procedure. Now it's not throwing any error when executing the proc.

Post #1254091
Posted Tuesday, June 11, 2013 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:27 AM
Points: 1, Visits: 0
pleas i have the same prob pleas write the procedure after modifying
Post #1462116
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse