Cursor code not working

[webuser]

The code below has worked for years on my SQL Server 2000 database. I recently moved the same code to SQL Server 2005 and it worked fine too. But I just installed the DB that contains this procedure on to a new SQL Server 2005 machine and for some reason I am getting the message 'A cursor with the name MyUserCursor does not exist'.

I checked the Database setting for default Cursors and it's set to Global. I also tried to add the word Global after Cursor just to make sure it was not using a Local cursor, but I still get the same message.

I believe the error is occuring because the OPEN MyUserCursor line is not seeing the cursor b/c it's being declared in the EXEC line.

But I emphasize that this code runs perfectly on other servers running both SQL Server 2000 & 2005.

DECLARE @MySQL VarChar(200), @MySQL2 VarChar(200)
Set @MySQL = 'Select Item' + @ItemNumber + ' From [Users] WHERE [User] = ''' + @MyUser + ''''
Set @MySQL2 = 'DECLARE MyUserCursor CURSOR FOR ' + @MySQL

EXEC (@MySQL2)
OPEN MyUserCursor
FETCH NEXT FROM MyUserCursor INTO @MyReturn
DEALLOCATE MyUserCursor


Thanks in advance!!!

 

[vongrunt]

Is that complete code? It fetches a single row...



------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[webuser]

Yes, I know it looks weird. The reason this code is being used is twofold:

1. The field needed to be returned depends on the Input Parameter.

2. The procedure needs to return a single scalar value and not a recordset.

Even if there is a better way, though, this type of code is all over the application. To change it would require a lot of work. And since it has always been working, I don't know why it would stop now...

Thanks.

 

[vongrunt]

I'll bet this is some other SQL2005 option in action - even if "default to local cursor" is set to "on", code should work with explicitely DECLARE... CURSOR GLOBAL.

("should work" = famous last words )

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[webuser]

Tried it. It is definitely not working even with the explicit Global. I tried simplifying the code too.

Set @MySQL2 = 'DECLARE MyUserCursor3 CURSOR GLOBAL FOR ' + @MySQL + ' OPEN MyUserCursor3'
EXEC (@MySQL2)

DEALLOCATE MyUserCursor3

So I'm declaring the cursor an dopening in the Exec statement which seems to work. All I try to do in this Sproc is Deallocate, but I get the error - it simply does not see it!

Help!!! (and thanks again)

 

[webuser]

Solved. my stupidity I guess. The select statement was simply not returning any rows, so I guess the server never created the cursor, and so the cursor was not found by the Stored Procedure. I would think, though, that the server would create an empty cursor - it looks like it does not...

Thank you for trying.