hi

Question

hi

eboucicaut 94437

Old Hand

Points: 330
More actions
May 26, 2014 at 6:46 am

#293672

someone can help me please,I have a stored procedure that executes a set of procedure in a cursor procedure. it always gives me an error message when the number of executed procedure exceeds 21.
DECLARE L_CURSOR_EXEC_PROCEDURE CURSOR
FOR
SELECT PROCEDURE_NAME FROM PROCEDURE_TO_EXECUTE WHERE EXECUTE_PROCEDURE=1
OPEN L_CURSOR_EXEC_PROCEDURE
FETCH NEXT FROM L_CURSOR_EXEC_PROCEDURE INTO @PROCEDURE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'EXEC ' +@PROCEDURE
SET NOCOUNT ON
EXEC sp_executeSQL @SQL
SET NOCOUNT ON
FETCH NEXT FROM L_CURSOR_EXEC_PROCEDURE INTO @PROCEDURE
END
CLOSE L_CURSOR_EXEC_PROCEDURE
DEALLOCATE L_CURSOR_EXEC_PROCEDURE
Error message:
Msg 16915, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 32
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' already exists.
Msg 16905, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 33
The cursor is already open.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 44
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 46
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 47
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I assume that the code you posted is part of a stored procedure. That stored procedure might be part of the procedures listed in here:

SELECT PROCEDURE_NAME FROM PROCEDURE_TO_EXECUTE WHERE EXECUTE_PROCEDURE=1

That means that it's calling itself and trying to create the same cursor again but it fails to do it.

you should review this to avoid recursivity that will only give you problems in this case.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

eboucicaut 94437 Old Hand Points: 330 More actions · Answer 2

SELECT PROCEDURE_NAME FROM PROCEDURE_TO_EXECUTE WHERE EXECUTE_PROCEDURE=1

"PROCEDURE_TO_EXECUTE" This is the table that I put the names of all the procedures that I will execute in my cursor

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

eboucicaut 94437 (5/26/2014)
someone can help me please,I have a stored procedure that executes a set of procedure in a cursor procedure. it always gives me an error message when the number of executed procedure exceeds 21.
DECLARE L_CURSOR_EXEC_PROCEDURE CURSOR
FOR
SELECT PROCEDURE_NAME FROM PROCEDURE_TO_EXECUTE WHERE EXECUTE_PROCEDURE=1
OPEN L_CURSOR_EXEC_PROCEDURE
FETCH NEXT FROM L_CURSOR_EXEC_PROCEDURE INTO @PROCEDURE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'EXEC ' +@PROCEDURE
SET NOCOUNT ON
EXEC sp_executeSQL @SQL
SET NOCOUNT ON
FETCH NEXT FROM L_CURSOR_EXEC_PROCEDURE INTO @PROCEDURE
END
CLOSE L_CURSOR_EXEC_PROCEDURE
DEALLOCATE L_CURSOR_EXEC_PROCEDURE
Error message:
Msg 16915, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 32
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' already exists.
Msg 16905, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 33
The cursor is already open.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 44
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 46
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.
Msg 16916, Level 16, State 1, Procedure INS_TTS_TO_BI_TRAVDB, Line 47
A cursor with the name 'L_CURSOR_EXEC_PROCEDURE' does not exist.

Hard to tell from what you have but the cursor you have is just fine. Judging from the errors above, I suspect that a piece of code in the PROCEDURE_TO_EXECUTE table has or makes reference to the code that trys to create and successfully drops the same named cursor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eboucicaut 94437 Old Hand Points: 330 More actions · Answer 4

Thank you, the problem is solved. I mistakenly put the main procedure that calls all other procedures in the table "PROCEDURE_TO_EXECUTE"