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.
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 112Internal 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 ONGOSET QUOTED_IDENTIFIER ONGO--Converts EventType Codes to StringALTER FUNCTION [dbo].[EventTypeConv](@string NVARCHAR(MAX))RETURNS NVARCHAR(MAX)ASBEGINRETURN CASE @string--VCP EventTypeWHEN '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 CAMERASWHEN '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'--UNKNOWNWHEN 'EV_TYPE_UNKNOWN' THEN 'ERROR - Event Type Unknown'--End ADPRO ProductsELSE 'ERROR - No Event Type Listed'ENDEND |
|
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 1The query processor ran out of stack space during query optimization. Please simplify the query. |
|
|
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 belowSELECT EventString FROM dbo.EventCodeToStringMapWHERE 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. |
|
|
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? |
|
|
|
|
|
|
|