Is this a bug, CURSOR_STATUS() always return -3 ?

Hi,
With this simple test, CURSOR_STATUS() function always return -3
use Northwind
go
if object_id('dbo.TestCursor') is not null
drop proc dbo.TestCursor
go
create proc dbo.TestCursor
as
declare @.ContactName varchar(50)
declare My_Curs cursor
fast_forward
for
select ContactName from dbo.Customers
open My_Curs
fetch next from My_Curs into @.ContactName
select @.ContactName as ContactName
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
close My_Curs
deallocate My_Curs
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
go
exec dbo.TestCursor
go
I have 02 questions
1. Is this is a bug with CURSOR_STATUS() function ?
2. If the SP fails in the middle and close / deallocate are not executed,
will SQL Server close and dealocate the resources of us or the next time the
SP is execute it will throw 'A cursor with the name ... already exists' ?
According to my tests SQL Server close and dealocate the cursor automaticaly
Thak you for your helpOn Fri, 30 Sep 2005 13:35:15 -0700, S.M wrote:

>Hi,
>With this simple test, CURSOR_STATUS() function always return -3
(snip)
>I have 02 questions
>1. Is this is a bug with CURSOR_STATUS() function ?
Hi S.M.,
No. Try changing your SELECT statement (at both locations) to
select
CURSOR_STATUS('local', 'My_Curs') as CursStatlocal,
CURSOR_STATUS('global', 'My_Curs') as CursStatglobal,
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable
(That is - change the names, AND include a query for global cursor).
>2. If the SP fails in the middle and close / deallocate are not executed,
>will SQL Server close and dealocate the resources of us or the next time th
e
>SP is execute it will throw 'A cursor with the name ... already exists' ?
>According to my tests SQL Server close and dealocate the cursor automaticaly[/color
]
You didn't test well, then. Include the following extra line after the
forst select but before the close command in the stored proc:
SELECT a FROM b
This won't generate an error when creating the proc, but will generate
an error when executing it. Now execute the proc twice in a row.
Results:
(first execution)
ContactName
----
Maria Anders
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
(second execution)
Server: Msg 16915, Level 16, State 1, Procedure TestCursor, Line 10
A cursor with the name 'My_Curs' already exists.
Server: Msg 16905, Level 16, State 1, Procedure TestCursor, Line 12
The cursor is already open.
ContactName
----
Ana Trujillo
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
Of course, when you declare the cursor to be local, it WILL be closed
and deallocated when the SP fails, since it goes out of scope.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)