Home Dashboard Directory Help
Search

TSQL case statement fails: Internal error: Server stack limit has been reached. (8631) by NigelCook


Status: 

Active


14
0
Sign in
to vote
Type: Bug
ID: 718795
Opened: 1/16/2012 7:02:57 AM
Access Restriction: Public
0
Workaround(s)
view
7
User(s) can reproduce this bug

Description

A Long case statement with 1740 cases that used to work on SQL2005 and SQL2008, now fails with :
Msg 8631, Level 17, State 1, Line 7
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
The case has the form:
declare @EnumString varchar(200) = 'string1'
select
case @EnumString
when 'string1' then 1000000
when 'string2' then 1000001
when 'string3' then 1000002
when 'string4' then 1000003
...
end

Is there any chance that this will be fixed before RTM or is this a new limitation to case statements or the number of literals?
Details
Sign in to post a comment.
Posted by George P Boutwell on 8/1/2013 at 9:02 AM
I have an SQL Where I get the same error (also worked on SQL Server 2008 and SQL Server 2008 R2 but not working on SQL Server 2012), which is not involving an CASE statement.

I have 'sanitized' names and such to protect the innocent and shortened the Script for brevity (added comments decribing the abbreviated statements where appropriate)

declare @dbname varchar(MAX), @SQL nvarchar(MAX)

declare c_dblist cursor for
-- if you want just user databases, include
-- where name >= 7 to this clause
select name from master.dbo.sysdatabases WHERE name like 'main[_]%' order by name;

open c_dblist
fetch next from c_dblist into @dbname
while @@fetch_status = 0
begin

print @dbname
SET @SQL = 'DECLARE @dbname NVARCHAR(MAX); SET @dbname = ''' + @dbname + ''';
use ['+@dbname+']
exec sp_replicationdboption @dbname = N'''+@dbname+''', @optname = N''publish'', @value = N''true''
;
use ['+@dbname+']
exec ['+@dbname+'].sys.sp_addlogreader_agent @job_login = N''DOMAIN\DBAServiceAccount'', @job_password = N''secret'', @publisher_additional_mode = 1, @job_name = null
;
-- Adding the transactional publication
use ['+@dbname+']
exec sp_addpublication @publication = N'''+@dbname+''', @description = N''Transactional publication of database '''''+@dbname+''''' from Publisher ''''SERVER\INSTANCE''''.'', @sync_method = N''concurrent'', @retention = 0, @allow_push = N''true'', @allow_pull = N''true'', @allow_anonymous = N''true'', @enabled_for_internet = N''false'', @snapshot_in_defaultfolder = N''true'', @compress_snapshot = N''false'', @ftp_port = 21, @allow_subscription_copy = N''false'', @add_to_active_directory = N''false'', @repl_freq = N''continuous'', @status = N''active'', @independent_agent = N''true'', @immediate_sync = N''true'', @allow_sync_tran = N''false'', @allow_queued_tran = N''false'', @allow_dts = N''false'', @replicate_ddl = 1, @allow_initialize_from_backup = N''false'', @enabled_for_p2p = N''false'', @enabled_for_het_sub = N''false''
;


exec sp_addpublication_snapshot @publication = N'''+@dbname+''', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N''DOMAIN\DBAServiceAccount'', @job_password = N''secret'', @publisher_additional_mode = 1


-- NOTE: Abbreviated, there are about 2400 lines just like this 'set' that follow this, only difference in the lines is that they involve a different table.
--        These lines are a slightly modified output from the Replication Wizard run on the DB in question.
use ['+@dbname+']
exec sp_addarticle @publication = N'''+@dbname+''', @article = N''TABLE1'', @source_owner = N''dbo'', @source_object = N''TABLE1'', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N''manual'', @destination_table = N''TABLE1'', @destination_owner = N''dbo'', @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_dboTABLE1'', @del_cmd = N''CALL sp_MSdel_dboTABLE1'', @upd_cmd = N''SCALL sp_MSupd_dboTABLE1''
;


'
EXEC sp_executesql @SQL

SET @dbname = REPLACE(@dbname, 'main_', 'additional_');
print @dbname
SET @SQL = 'DECLARE @dbname NVARCHAR(MAX); SET @dbname = ''' + @dbname + ''';
use ['+@dbname+']
exec sp_replicationdboption @dbname = N'''+@dbname+''', @optname = N''publish'', @value = N''true''
;
use ['+@dbname+']
exec ['+@dbname+'].sys.sp_addlogreader_agent @job_login = N''DOMAIN\DBAServiceAccount'', @job_password = N''secret'', @publisher_additional_mode = 1, @job_name = null
;
-- Adding the transactional publication
use ['+@dbname+']
exec sp_addpublication @publication = N'''+@dbname+''', @description = N''Transactional publication of database '''''+@dbname+''''' from Publisher ''''SERVER\INSTANCE''''.'', @sync_method = N''concurrent'', @retention = 0, @allow_push = N''true'', @allow_pull = N''true'', @allow_anonymous = N''true'', @enabled_for_internet = N''false'', @snapshot_in_defaultfolder = N''true'', @compress_snapshot = N''false'', @ftp_port = 21, @allow_subscription_copy = N''false'', @add_to_active_directory = N''false'', @repl_freq = N''continuous'', @status = N''active'', @independent_agent = N''true'', @immediate_sync = N''true'', @allow_sync_tran = N''false'', @allow_queued_tran = N''false'', @allow_dts = N''false'', @replicate_ddl = 1, @allow_initialize_from_backup = N''false'', @enabled_for_p2p = N''false'', @enabled_for_het_sub = N''false''
;


exec sp_addpublication_snapshot @publication = N'''+@dbname+''', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N''DOMAIN\DBAServiceAccount'', @job_password = N''secret'', @publisher_additional_mode = 1



-- NOTE: Abbreviated, there are about 140 lines just like this 'set' that follow this, only difference in the lines is that they involve a different table.
--        These lines are a slightly modified output from the Replication Wizard run on the DB in question.
use ['+@dbname+']
exec sp_addarticle @publication = N'''+@dbname+''', @article = N''ADDLTABLE1'', @source_owner = N''dbo'', @source_object = N''ADDLTABLE1'', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N''manual'', @destination_table = N''ADDLTABLE1'', @destination_owner = N''dbo'', @vertical_partition = N''false''
;




'
EXEC sp_executesql @SQL

fetch next from c_dblist into @dbname
end
close c_dblist
deallocate c_dblist
Posted by Guruprasad V on 6/12/2013 at 12:02 AM
Same here, When I run SQL Statement which has sequence of CASE statements (Search condition), it fails beyond 1329 case

SELECT    CustomerKey,
CASE
WHEN Code='SAP' AND Area='APAC' THEN 'Asia' --1
WHEN Code='SAP' AND Area='Canada' THEN 'NA' --2
.
.
WHEN Code='SAP' AND Area='AUD' THEN 'NA' --1329
END

Msg 8631, Level 17, State 1, Line 6
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
Posted by Nick81 on 12/11/2012 at 9:53 PM
Just to clarify, I would like to mention that in my case, the error showed when executing a stored procedure with 200 arguments. This is a simple SP with an update statement taking in the 200 arguments and using them to update the values of the 200 fields of a single table in our database.

I actually managed to find a temporary fix for this by splitting the update statement into 2 separate update statements. I found out that the "limit" for the number of arguments for a single update statement was of 153/154. Adding just a single argument to this would give the error.
In other terms, the issue was not with the SP call itself but with the update statement. The SP still takes 200 arguments without giving the error.
Posted by Nick81 on 12/11/2012 at 12:46 AM
This issue was first reported back in January and now, testing the same case on SP1 CU#1 returns the same error message...

Is there any way at all to give some priority to this issue? I am sure I am not the only one who'd be glad to get some feedback from Microsoft on this.

Searching the internet for similar cases returns nothing. This is the one page with people reporting it...
Posted by Janet Esposito on 11/29/2012 at 11:37 AM
Here's a new wrinkle on this problem.

I have one procedure where the creation script will run fine on some computers using SQl Server 2012 but we get the server stack limit error on others. It's only 450 lines but does contain a few dynamic sql statements.

My desktop computer is a 64-bit machine and laptop is 32-bit. It runs fine on the 64-bit machine but fails on the 32-bit machine. Both machines can create it just fine under SQL Server 2008.
Posted by Nick81 on 10/22/2012 at 1:45 AM
We're facing the same issue as well. Did anyone find a workaround or a fix which doesn't require to rewrite the stored procedures completely... ?
Posted by Serg2121 on 10/12/2012 at 11:29 AM
I'm getting the same error, is any solution at this time?

This is a big problem, beacuse I'm migrating a lot of stored procedures and some of them has nested case statements.

I hope you can help us with this issue.
Posted by Chris DW on 8/9/2012 at 6:11 AM
SQL Server 2012 (11.0.2100)

I'm seeing this on a view with case statements that calls a complex function with a large case statement, the function if called directly does not give this error. This is preventing us from adopting SQL Server 2012
Posted by NewVision1 on 1/26/2012 at 2:56 AM
SQL Server 2012 (Denali)

I retrive the same error (Msg 8631 - Server stack limit has been reached...) in case of executing of stored procedure with 1024 params. I don't meet the error with SQL 2008.

EXECUTE @RC = [DB1].[dbo].[TestDenali20121]
@P0=1,
@P1=1,
@P2=1,
@P3=1,
...
@P1023=1,
@POUT = @POUT OUTPUT

SELECT    @POUT as N'@POUT'
Posted by Microsoft on 1/23/2012 at 3:19 PM
Hello Nigel,
Thanks for reporting the issue. We will invetigate and let you know what we find.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
SQL2012 Example of case statement error.sql 1/23/2012 53 KB