0

I have one scalar-valued function, func-A and inline table-valued function, func-B. func-A calls func-B and func-B again calls func-A recursively. but the recursion level will never be too deep. It must always be 2 levels. For example, func-A calls func-B. And func-B again calls func-A and that will be the end.

This is working OK on my local SQL Server 2008 R2 but failing at production server. Error Message is displaying "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". But strangely, on production server, this problem is happening to certain database instances only. some instances are working OK.

How do I overcome this problem? (I think I may need to turn on some options, for example like "RECURSIVE_TRIGGERS". )Thanks In Advance.

Pyae Phyo Aung
  • 828
  • 2
  • 10
  • 29
  • 1
    it sounds like you have data quality issue in production. If you can formulate "must be 2 levels recursion" into a business rule, you will be able to create a validation query to find data anomalies. – Bulat Jul 29 '15 at 10:38

1 Answers1

0

Here's some simple steps to diagnose the recursive calls:

  • Use SQL profiler and capture a set of inputs that cause the issue to manifest.
  • Connect via Management Studio and create a new query window, and execute the command/verify it fails.
  • Create a SQL Server Profiler session, with the following options:
    • Column Filter - SPID Is Equal to the SPID for your SSMS window
    • Include Event: Statement Started
    • Include Event: Sp:StmtCompleted

This will show you individual statements in your UDF's are executing, allowing you to home in on the path. Another option is to simply edit the procedure to PRINT the parameters at the top, allowing you to home in on the recursion depth issue at the data level.

Steve Gray
  • 450
  • 2
  • 4