Nesting level limit error in SQL Server 2014

Question

We have the following SQL Server stored procedure:

ALTER PROCEDURE [dbo].[up_get_id]
    (@KeyName VARCHAR(30), @ID INTEGER OUTPUT)
AS
BEGIN
    DECLARE @SEQ_NAME VARCHAR(120);
    SET @SEQ_NAME = 'seq_dbk_' + @KeyName;

    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'set @ID = next value for ' + @SEQ_NAME;

    EXEC sp_executesql @SQL, N'@ID int out', @ID = @ID output;

    RETURN 0;
END
GO

Sometimes (not always, probably under high loading) after calling the stored procedure:

declare @P1 int
exec up_get_id  @KeyName = 'KEY', @ID = @P1 output
select @P1 Result

the client is getting an error:

Warning: Fatal error 217 occurred at Nov 21 20117 12:54PM. Note the error and time, and contact your system administrator.

Also, the following message appears in SQL Server log:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 2).

Any ideas of what is wrong?

Server version:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)   
Jun 9 2015 12:06:16   
Copyright (c) Microsoft Corporation  
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Exactly written "limit 2"? show full sql error with number and object — Stanislav Kundii, Nov 23 '17 at 10:38
Yes, exactly "limit 2", it's not a typo. Actually, there are 2 error messages: 1st (getting by the client): "Warning: Fatal error 217 occurred ... " and 2nd which appears only in SQL Server Log: "Error: 217, Severity: 39, State: 16. Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 2)." — Alexander Sokolov, Nov 23 '17 at 12:17
As I already said this message is from SQL Server log. I agree 2 things are very strange there: 1) "limit 2" 2) "Severity: 39" — Alexander Sokolov, Nov 23 '17 at 15:42

score 1 · Accepted Answer · answered Dec 14 '17 at 09:56

Аnd unanswered response :)

If COMPATIBILITY_LEVEL 100 and less error

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 2).

110 and more

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

So you set ROWCOUNT <> 0

Nesting level limit error in SQL Server 2014

1 Answers1