Roxanne25
asked on
Stored Proc Error Message: nesting level exceeded
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. :/
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. :/
ASKER