Solved

Stored Proc Error Message:  nesting level exceeded

Posted on 2012-03-21
3
380 Views
Last Modified: 2012-06-27
Hi, I am getting this error message on my stored procedure:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

However, I'm not calling anything in it or referencing a function or trigger...

The procedure was compiling correctly but I made a change to the cursor and removed the "Fast Forward" in the declaration.  Now when I try to run it I get this error.

I am at a loss as to why the procedure isn't working properly.  We ran it a few weeks ago and it worked perfectly and inserted records as expected.  However I've been monitoring it and it hasn't done anything since the day we tested it.  

Before I took out the "Fast Forward" in the cursor declaration, I tested it with one record ... it didn't do anything.  I then tested it again with the same record and it worked as expected.  It didn't make any sense to me why it didn't work the first time and then worked the second time.  

So, I thought maybe it was something buggy with my cursor... so I removed the fast forward...and now I'm getting the wierd error message.

Any direction is appreciated... I'm kind of at a loss as to why this isn't working.  Normally, we're not getting any error messages or anything it just seems to skip over all the IF statements even though there are records in the cursor.

ALTER PROCEDURE [dbo].[sp_aesip_post_ZLIN_LIW_Updates] AS

BEGIN

	DECLARE @QUERY AS VARCHAR(4000)
	DECLARE @MESSAGE AS VARCHAR(4000)
	DECLARE @SSN AS VARCHAR(6)
	DECLARE @DIN AS VARCHAR(15)
	DECLARE @LIN AS VARCHAR(6)
	DECLARE @COUNT AS INT
	DECLARE @ZLIN_DEL AS CHAR(6)
	DECLARE @I_CAT AS CHAR(1)
	
	DECLARE INSERT_CURSOR CURSOR FOR
	SELECT LIN,SSN6,DISPLAY_ISSUE_NUM
	FROM TEMP_AESIP_POST_ZLIN_INS
	
	OPEN INSERT_CURSOR
	
	FETCH NEXT FROM INSERT_CURSOR
	INTO @LIN,@SSN,@DIN
	
	WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SET @COUNT = (SELECT COUNT(*) FROM LIWLINK2..SSN.SSN_LIN_REQUEST WHERE LIN = @LIN)
			SET @QUERY = 'INSERT OPENQUERY(LIWLINK2, ''SELECT SSN6,LIN,R_CAT,SLAMIS_RQST,RQST_TYPE,LST_UPDT FROM SSN.SSN_LIN_REQUEST'') VALUES (''' + @SSN + ''',''' + @LIN + ''',''P'',''' + @DIN + ''',''1'',GETDATE())'
	
			IF @COUNT = 0
				BEGIN
					PRINT @QUERY
					EXEC (@QUERY)
					SELECT @QUERY AS QUERY INTO TEMP_QUERY_STRING
					SET @QUERY = (SELECT REPLACE(QUERY,'''','''''') FROM TEMP_QUERY_STRING)
					SET @MESSAGE = 'INSERT INTO LIW_UPDATE_LOG VALUES(''ZLIN'',''' + @DIN + ''',''SSN_LIN_REQUEST'',''INSERT'',''sp_aesip_post_ZLIN_LIW_Updates'',''' + @QUERY + ''',GETDATE(),''PROCESSED'',NULL)'
					EXEC (@MESSAGE)
					PRINT @MESSAGE	
				END
			ELSE
				BEGIN
					SELECT @QUERY AS QUERY INTO TEMP_QUERY_STRING
					SET @QUERY = (SELECT REPLACE(QUERY,'''','''''') FROM TEMP_QUERY_STRING)
					SET @MESSAGE = 'INSERT INTO LIW_UPDATE_LOG VALUES(''ZLIN'',''' + @DIN + ''',''SSN_LIN_REQUEST'',''INSERT'',''sp_aesip_post_ZLIN_LIW_Updates'',''' + @QUERY + ''',GETDATE(),''UNRESOLVED'',''DUPLICATE LIN'')'
					EXEC (@MESSAGE)
					PRINT @MESSAGE
				END
				
		FETCH NEXT FROM INSERT_CURSOR
		INTO @LIN,@SSN,@DIN
		
		DROP TABLE TEMP_QUERY_STRING
		
		END
	
	CLOSE INSERT_CURSOR
	DEALLOCATE INSERT_CURSOR

	DECLARE DELETE_CURSOR CURSOR FOR
		SELECT SSN6,LIN,I_CAT,DISPLAY_ISSUE_NUM
		FROM TEMP_AESIP_POST_ZLIN_DEL
		
	OPEN DELETE_CURSOR
	
	FETCH NEXT FROM DELETE_CURSOR 
	INTO @SSN, @ZLIN_DEL,@I_CAT,@DIN
	
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @QUERY = 'INSERT OPENQUERY(LIWLINK2, ''SELECT SSN6,LIN,R_CAT,SLAMIS_RQST,RQST_TYPE,LST_UPDT FROM SSN.SSN_LIN_REQUEST'') VALUES (''' + @SSN + ''',''' + @ZLIN_DEL + ''',''' + @I_CAT + ''',''' + @DIN + ''',''3'',GETDATE())'
			EXEC (@QUERY)
			
			SELECT @QUERY AS QUERY INTO TEMP_QUERY_STRING_2
			SET @QUERY = (SELECT REPLACE(QUERY,'''','''''') FROM TEMP_QUERY_STRING_2)
			
			SET @MESSAGE = 'INSERT INTO LIW_UPDATE_LOG VALUES(''ZLIN'',''' + @DIN + ''',''SSN_LIN_REQUEST'',''DELETE'',''sp_aesip_post_ZLIN_LIW_Updates'',''' + @QUERY + ''',GETDATE(),''PROCESSED'',NULL)'
			EXEC (@MESSAGE)
			
			PRINT @MESSAGE
			
			FETCH NEXT FROM DELETE_CURSOR 
		    INTO @SSN, @ZLIN_DEL,@I_CAT,@DIN
		    
		    DROP TABLE TEMP_QUERY_STRING_2
			
		END
	
	CLOSE DELETE_CURSOR
	DEALLOCATE DELETE_CURSOR

	TRUNCATE TABLE TEMP_AESIP_POST_ZLIN_DEL
	TRUNCATE TABLE TEMP_AESIP_POST_ZLIN_INS

END

Open in new window

0
Comment
Question by:Roxanne25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:Roxanne25
ID: 37748295
Also, just wanted to note I coded this in 2008 but our production server is SQL 2000.  I think something is goofy with my cursor declaration.... :/
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 37748833
Does it not tell you any more? You have two cursors in there - can you work out where your failure is happening ?  (Try running it with one cursor commented out, and then the other!)

Does the error happen when you run the SP from SSMS or only when from some other SP?
0
 

Author Comment

by:Roxanne25
ID: 37749733
No, doesn't give any more info than that... I don't run it from within another SP.  It runs as a stand alone.  

Commenting out the second cursor seems to have allowed it to run without the error... but I'm still not convinced the cursor is performing as it should.  I separated the two cursors into two stored procedures now... so, I guess I'll have to watch it and see if it continues to act up. :/
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question