August 1, 2002 at 8:36 am
i'm trying to use the sp_cursorclose
procedure. Does anyone know what parameters i need to use this procedure?
thanks rich
RICHARD KIRMSS
RICHARD KIRMSS
August 1, 2002 at 8:42 am
The system stored procedure sp_cursorclose is an internal system procedure. Typically when we want to close a cursor and remove it from memory we do the following:
CLOSE <cursor name>
DEALLOCATE <cursor name>
Is there a reason you need to use sp_cursorclose explicitly?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 1, 2002 at 8:48 am
well in my stored procedure that i created i opening and closing 2 different cursors numourous times. And there are times when i get a a message stating:
Server: Msg 16915, Level 16, State 1, Procedure sp_grant_revoke_perms_roles, Line 65
A cursor with the name 'tables_cursor' already exists.
so i thought well maybe i would try the sp_cursorclose. I checked and re-checked my program and everytime that I'm done with the cursor i do close and i deallocate it and I still get that message. any suggestions?
RICHARD KIRMSS
RICHARD KIRMSS
August 1, 2002 at 9:11 am
Is there the possibility of your stored procedure being executed by more than one connection at the same time? If so, are you using local or global cursors?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 1, 2002 at 9:16 am
i'm not sure...i'm just trying to run the procedure form the query analyzer. Inside the procedure is where i created the cursors. The Cusors are local to the procedure.
RICHARD KIRMSS
RICHARD KIRMSS
August 1, 2002 at 12:03 pm
ok i figured out what i got to do. BUt i need to know the status of my cursors. is there a way i can find out the status of my cursors? thanks
rich
RICHARD KIRMSS
RICHARD KIRMSS
August 2, 2002 at 2:14 pm
use CURSOR_STATUS
From BOL:
CURSOR_STATUS
A scalar function that allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set for a given parameter.
Syntax
CURSOR_STATUS
(
{ 'local' , 'cursor_name' }
| { 'global' , 'cursor_name' }
| { 'variable' , 'cursor_variable' }
)
Arguments
'local'
Specifies a constant that indicates the source of the cursor is a local cursor name.
'cursor_name'
Is the name of the cursor. A cursor name must conform to the rules for identifiers.
'global'
Specifies a constant that indicates the source of the cursor is a global cursor name.
'variable'
Specifies a constant that indicates the source of the cursor is a local variable.
'cursor_variable'
Is the name of the cursor variable. A cursor variable must be defined using the cursor data type.
Return Types
smallint
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply