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 help

On Fri, 30 Sep 2005 13:35:15 -0700, S.M wrote:

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).


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

select only first row
Unknown Columns!
Simple query?
order by
Can someone explain why this is not an error?