Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Cannot create cursors on a database

Răspuns Cannot create cursors on a database

  • Tuesday, October 09, 2012 4:18 AM
     
     
    Sign In to Vote
    0

    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

    and run it returns "Command(s) completed sucessfully."

    I then run

    open mycursor

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

All Replies

  • Tuesday, October 09, 2012 4:43 AM
     
      Has Code
    Sign In to Vote
    0

    Hello Phil,

    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 Helper

    Blog Xing

     
  • Tuesday, October 09, 2012 6:08 AM
     
     
    Sign In to Vote
    0

    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 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Using 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


    My blog