Hi Everyone,
Here is a new error condition I thought I’d let you know about.
One of my customers purchased my SnapShot! documentation tool and was receiving Generic SQL Server errors on what appeared to be the simplest queries.
After looking at the platform trace, I see this error message:
Exception: System.Data.SqlClient.SqlException (0x80131904): Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
What in the heck does that even mean?
After a small amount of searching, I ran into this Microsoft KB article.
It seems that the error was corrected in individual rollups for both SQL Server 2012 SP1 and SP2.
Root Cause
This was probably caused by a GINORMOUS SQL Case statement. This particular customer has what I am fairly certain to be the largest number of custom entities ever added to Dynamics CRM and SQL Server was having issues when creating a query that looks like this:
- select
- top 251 coalesce("LL0".Label,"systemform0".Name ) as "name"
- , "systemform0".Type as "type"
- , "systemform0".FormId as "formid"
- , "systemform0".ObjectTypeCode as "objecttypecode"
- , coalesce("LL1".Label,"systemform0".Description ) as "description"
- , "systemform0".IsDefault as "isdefault"
- , "systemform0".FormXml as "formxml"
- from
- SystemForm as "systemform0"
- left outer join LocalizedLabelView as "LL0" on ("LL0".ObjectId = "systemform0".FormId and "LL0".LanguageId = 1033 and "LL0".ObjectColumnName = 'Name' )
- left outer join LocalizedLabelView as "LL1" on ("LL1".ObjectId = "systemform0".FormId and "LL1".LanguageId = 1033 and "LL1".ObjectColumnName = 'Description' )
- where
- ((("systemform0".Type != 4 or "systemform0".Type is null) and ((("systemform0".Type != 3 or "systemform0".Type is null) and ("systemform0".Type != 8 or "systemform0".Type is null))))) order by
- case "systemform0".ObjectTypeCode when 10000 then 'New Entity'
