November 21, 2013 at 1:01 pm
I have a stored procedure that is giving me the error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
The procedure is not recursive but it contains a recursive CTE. The error is coming from a call to that adds an error notification inside a "begin catch"/"end catch". The CTE is between "begin try"/"end try".
Can anyone help with this? Thanks
November 21, 2013 at 1:14 pm
Without seeing the procedure, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2013 at 1:23 pm
Here's a snippet of the procedure. The error is pointing to MyNotificationProcedure.
create procedure MyProc
as
begin try
-- SQL Code
;with MyRecursion
as
(
-- Anchor
union all
-- Recursion
)
select * from MyRecursion
-- SQL Code
return 0
end try
begin catch
exec MyNotificationProcedure @msg = 'Error in MyRecursion'
return -1
end catch
go
November 21, 2013 at 1:26 pm
nelson.muller (11/21/2013)
Here's a snippet of the procedure. The error is pointing to MyNotificationProcedure.create procedure MyProc
as
begin try
-- SQL Code
;with MyRecursion
as
(
-- Anchor
union all
-- Recursion
)
select * from MyRecursion
-- SQL Code
return 0
end try
begin catch
exec MyNotificationProcedure @msg = 'Error in MyRecursion'
return -1
end catch
go
This doesn't help. Remember we can't see your screen, we have no idea what your tables look like and we don't know what you are trying to do.
About all I can tell you is that you have recursion going at least to 33 levels which raises the exception that you are seeing.
Please take a few minutes and read the first article in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2013 at 1:38 pm
I understand the best practices and I am quite experienced with SQL server. I haven't seen this error before. Unfortunately the procedure is over 2,000 lines long and has a very complex process in a complex data model. Let me try to rephrase my question. The error is:
Msg 217, Level 16, State 1, Procedure MyNotification, Line 75
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
The call to MyNotification is done within a begin/end catch. Does that mean there is a nesting inside MyNotification, or is the error because there is a recursive CTE between the begin/end try? Thanks again.
November 21, 2013 at 2:12 pm
nelson.muller (11/21/2013)
I understand the best practices and I am quite experienced with SQL server. I haven't seen this error before. Unfortunately the procedure is over 2,000 lines long and has a very complex process in a complex data model. Let me try to rephrase my question. The error is:Msg 217, Level 16, State 1, Procedure MyNotification, Line 75
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
The call to MyNotification is done within a begin/end catch. Does that mean there is a nesting inside MyNotification, or is the error because there is a recursive CTE between the begin/end try? Thanks again.
There is nesting of some kind or other going on somewhere in your code right around line 75.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2013 at 2:41 pm
This is the error I get.
Msg 156, Level 15, State 1, Procedure MyProc, Line 13
Incorrect syntax near the keyword 'union'.
November 21, 2013 at 3:03 pm
benjamin.reyes (11/21/2013)
This is the error I get.Msg 156, Level 15, State 1, Procedure MyProc, Line 13
Incorrect syntax near the keyword 'union'.
The OP obviously posted pseudocode. There is nothing like syntax in there that would actually work. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2013 at 3:12 pm
😉
November 21, 2013 at 3:31 pm
Thanks everyone. I did find what the issue was, and that's because I was running the procedure after a SET FMTLONLY ON, which forced every if statement to run. Although at run time the procedure is protected against recursion, the FMTONLY caused SQL Server to "think" it had recursion.
Thanks everyone for the responses.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply