SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Internal error: Server stack limit has been reache		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harlingtonthewizard
Constraint Violating Yak Guru

Australia
345 Posts
Posted - 02/10/2013 :  20:15:13  Show Profile  Reply with Quote
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

Australia
345 Posts
Posted - 02/10/2013 :  21:08:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1490 Posts
Posted - 02/10/2013 :  21:36:21  Show Profile  Reply with Quote
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

Australia
345 Posts
Posted - 02/11/2013 :  23:11:56  Show Profile  Reply with Quote
Thanks James. This solution does work. Does anyone know why there is a limit?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000