Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Internal error: Server stack limit has been reache

Author  Topic 

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-10 : 20:15:13
I am receiving the following error:

Msg 8631, Level 17, State 1, Procedure EventImages, Line 112
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.


After some investigation I found it is due to the amount of 'WHEN' statements in my scalar valued function that converts one string to another. If I go over a certain amount of when statements it fails. If I reduce the amount it works. How and why does the amount of when statements affect this procedure if it only returns one string?

Here is it's use in a stored procedure:

Select DISTINCT [SiteID], [SiteName], #t.EventID, [CameraNumber], [CameraName], dbo.EventTypeConv ([EventType]) As [EventType]

Here is the code. I have reduced the amount of 'when' statements to make it manageable here. The actual code has probably around 2000-3000 'when' statements.

USE [VC]
GO
/****** Object: UserDefinedFunction [dbo].[EventTypeConv] Script Date: 02/11/2013 11:07:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Converts EventType Codes to String
ALTER FUNCTION [dbo].[EventTypeConv](@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)

AS

BEGIN
RETURN CASE @string

--VCP EventType
WHEN 'EV_TYPE_VCP_SYSTEM_MONITOR_DEBUG' THEN 'SYSTEM MONITOR DEBUG'
WHEN 'EV_TYPE_VCP_SYSTEM_MONITOR_ERROR' THEN 'SYSTEM MONITOR ERROR'
WHEN 'EV_TYPE_VCP_SYSTEM_MONITOR_INFO' THEN 'SYSTEM MONITOR INFO'
WHEN 'EV_TYPE_VCP_SYSTEM_MONITOR_WARN' THEN 'SYSTEM MONITOR WARN'
WHEN 'EV_TYPE_VCP_SYSTEM_MONITOR_FATAL' THEN 'SYSTEM MONITOR FATAL'
WHEN 'EV_TYPE_VCP_SITE_ACKNOWLEDGEMENT' THEN 'SITE ACKNOWLEDGEMENT'
WHEN 'EV_TYPE_VCP_SITEPULSE' THEN 'SITEPULSE'
WHEN 'EV_TYPE_VCP_SITEPULSE_PRIMARY' THEN 'SITEPULSE PRIMARY'
WHEN 'EV_TYPE_VCP_SITEPULSE_BACKUP' THEN 'SITEPULSE BACKUP'
WHEN 'EV_TYPE_VCP_OPERATOR_OBSERVATION' THEN 'OPERATOR OBSERVATION'
WHEN 'EV_TYPE_VCP_SITE_CONNECT' THEN 'SITE CONNECT'
WHEN 'EV_TYPE_VCP_SITE_DISCONNECT' THEN 'SITE DISCONNECT'
WHEN 'EV_TYPE_VCP_SITE_SELECT' THEN 'SELECT SITE FROM ASL'
WHEN 'EV_TYPE_VCP_SITE_RELEASE' THEN 'RELEASE SITE TO ASL'
WHEN 'EV_TYPE_VCP_CMS_CONNECT' THEN 'CMS CONNECT'
WHEN 'EV_TYPE_VCP_CMS_DISCONNECT' THEN 'CMS DISCONNECT'
WHEN 'EV_TYPE_VCP_HD_STORAGE_WARNING' THEN 'HARD DRIVE STORAGE WARNING'
WHEN 'EV_TYPE_VCP_HD_STORAGE_FULL' THEN 'HARD DRIVE STORAGE FULL'
WHEN 'EV_TYPE_VCP_DB_VC_STORAGE_WARNING' THEN 'VC DATABASE STORAGE WARNING'
WHEN 'EV_TYPE_VCP_DB_VC_STORAGE_FULL' THEN 'VC DATABASE STORAGE FULL'
WHEN 'EV_TYPE_VCP_DB_VCLOG_STORAGE_WARNING' THEN 'VCLOG DATABASE STORAGE WARNING'
WHEN 'EV_TYPE_VCP_DB_VCLOG_STORAGE_FULL' THEN 'VCLOG DATABASE STORAGE FULL'
WHEN 'EV_TYPE_VCP_SITE_MISCONFIGURATION' THEN 'SITE MISCONFIGURATION'
WHEN 'EV_TYPE_VCP_STILL_IMAGE' THEN 'STILL IMAGE'
WHEN 'EV_TYPE_VCP_SITE_INCOMPATIBILITY' THEN 'SITE INCOMPATIBILITY - UPGRADE FastTrace 2 TO V2.01 OR GREATER'
WHEN 'EV_TYPE_VCP_SITE_NO_CMS_PRIORITY' THEN 'SITE NO CMS PRIORITY'

--Start Hydra VSK Products

---HYDRA CAMERAS
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_SYNC_LOSS' THEN 'CAM01 SYNC LOSS'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_SABOTAGE' THEN 'CAM01 SABOTAGE'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_RECORDING' THEN 'CAM01 RECORDING'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_ANALYTICS_ERROR' THEN 'CAM01 ANALYTICS ERROR'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_ANALYTICS_BAD_SCENE' THEN 'CAM01 ANALYTICS BAD SCENE'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_CONTRAST_FAULT' THEN 'CAM01 CONTRAST FAULT'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_RECORDING_ERROR' THEN 'CAM01 RECORDING ERROR'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_NO_RAW_VIDEO' THEN 'CAM01 NO RAW VIDEO'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_EVENT' THEN 'CAM01 EVENT'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_PRESIDIUM_DETECTION' THEN 'CAM01 PRESIDIUM DETECTION'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1' THEN 'CAM01 MOTION1'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_MOTION2' THEN 'CAM01 MOTION2'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_MOTION3' THEN 'CAM01 MOTION3'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_MOTION4' THEN 'CAM01 MOTION4'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT1' THEN 'CAM01 INPUT1'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT2' THEN 'CAM01 INPUT2'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT3' THEN 'CAM01 INPUT3'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT4' THEN 'CAM01 INPUT4'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT5' THEN 'CAM01 INPUT5'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT6' THEN 'CAM01 INPUT6'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT7' THEN 'CAM01 INPUT7'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_INPUT8' THEN 'CAM01 INPUT8'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_LOITERING_DETECTION' THEN 'CAM01 LOITERING DETECTION'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_ANALYTICS_DETECTION' THEN 'CAM01 ANALYTICS DETECTION'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_PERIMETER_DETECTION' THEN 'CAM01 PERIMETER DETECTION'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_FIRE_PRE_ALARM' THEN 'CAM01 FIRE PRE ALARM'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_FIRE_ALARM' THEN 'CAM01 FIRE ALARM'
WHEN 'EV_TYPE_HY_CAMERAS_CAM01_FIRE_VERIFICATION' THEN 'CAM01 FIRE VERIFICATION'

--UNKNOWN
WHEN 'EV_TYPE_UNKNOWN' THEN 'ERROR - Event Type Unknown'
--End ADPRO Products

ELSE 'ERROR - No Event Type Listed'

END

END

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-10 : 21:08:58
If I change it to an inline function. I get this;

Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 21:36:21
Instead of using the series of CASE expressions, create a table like shown below and insert the mappings into that table. You can create the insert statements easily in Excel or an editor that has regular expression capabilities by copying the text from your function and editing.
CREATE TABLE dbo.EventCodeToStringMap
(
EventCode NVARCHAR(256) NOT NULL PRIMARY KEY,
EventString NVARCHAR(256) NOT NULL
);
INSERT INTO dbo.EventCodeToStringMap VALUES
('EV_TYPE_VCP_SYSTEM_MONITOR_DEBUG', 'SYSTEM MONITOR DEBUG');
-- and so on for all the pairs


Now that you have the table, returning the values is easy - a simple select like shown below
SELECT EventString FROM dbo.EventCodeToStringMap
WHERE EventCode = @string;

In fact, I would suggest that you don't even create a function to do this. Join to your new table where you need to do the mapping. A function call is more expensive - because it gets called for each row in your query.

Also, as a best practice, if you don't need NVARCHAR(MAX), use NVARCHAR with a smaller length.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-11 : 23:11:56
Thanks James. This solution does work. Does anyone know why there is a limit?
Go to Top of Page
   

- Advertisement -