Tuesday, October 09, 2012 4:18 AM
One of my test databases has developed an issue that I am not sure why it is failing.
My system is SQL 2008 R2 SP1.
On one of my databases if I declare a cursor as such
declare mycursor cursor for select customerid from customer
I then run
and I am met with
A cursor with the name 'mycursor' does not exist.
If I run select * from sys.syscursors there is nothing in the table.
This happens no matter what cursor I try to create on this one database.
If I change to another of my test databases on the same system and run the same code it works as expected.
It appears that this issue is local to the one database.
I have tried:
- Restarting the SQL Server
- Restarting the Computer
- Backing up and restoring the affected database
None of which have resolved the issue.
I Restored an older backup of the affected data base on the same computer and the older backup works as expected. It does not have the same issue.
I ran a DBCC check on the affected database and it is not showing any corruption.
I am at a loss to explain this behaviour and I am not sure how to proceed from here.
Regards, Phil Doensen
- Moved by Tom PhillipsModerator Tuesday, October 09, 2012 2:55 PM TSQL question (From:SQL Server Database Engine)
Tuesday, October 09, 2012 4:43 AM
Seems you try to execute the script line by line; this won't work, you have to executed all as a batch.
Open a new query window and copy the SQL script below to it. Hit key F5 and you will see, it works; but only as a batch.
declare @name sysname; declare mycursor cursor for select name from sys.objects open mycursor fetch next from mycursor into @name WHILE @@FETCH_STATUS = 0 BEGIN print @name; fetch next from mycursor into @name end close mycursor deallocate mycursor
Anyway, you should avoid using cursor in SQL, it's slow.
Olaf HelperBlog Xing
Tuesday, October 09, 2012 6:08 AM
That didn't quite explain my problems.
Somewhere along the way I had set the Default Cursor to Local and not Global on that one database.
Thanks for your help anyway.
Regards, Phil Doensen
Wednesday, October 10, 2012 8:23 PMModeratorUsing LOCAL cursors is a better behavior, so you should always add LOCAL to your cursor declaration. Of course, it will not let you debug the way you want, but it will save you from many other issues.
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Monday, October 15, 2012 4:49 AM