Professional Web Applications Themes

A cursor can't be closed - Microsoft SQL / MS SQL Server

Hi, I am working on a stored procedure and it requires cursor to be used in the procedure. For some reasons, I can't close the cursor even using both CLOSE and DEALLOCATE at the end of the procedure. Anyone has any idea what might be the cause. The procedure needs to update the data in a different server. The stored procedure is executed on SQL Server 7.0 with compatibility 65. The Remote server is SQL Server 7.0 with compatibility 70. Followings are the error I got: Server: Msg 16915, Level 16, State 1, Procedure sp_test, Line 43 A cursor with ...

  1. #1

    Default A cursor can't be closed

    Hi,

    I am working on a stored procedure and it requires cursor
    to be used in the procedure. For some reasons, I can't
    close the cursor even using both CLOSE and DEALLOCATE at
    the end of the procedure. Anyone has any idea what might
    be the cause. The procedure needs to update the data in a
    different server. The stored procedure is executed on SQL
    Server 7.0 with compatibility 65. The Remote server is SQL
    Server 7.0 with compatibility 70. Followings are the error
    I got:

    Server: Msg 16915, Level 16, State 1, Procedure sp_test,
    Line 43
    A cursor with the name 'test_cursor' already exists.
    Server: Msg 16905, Level 16, State 1, Procedure sp_test,
    Line 53
    The cursor is already open.
    deallocated cursor

    Any help would be greatly appreciated!

    Shane
    Shane Guest

  2. #2

    Default Re: A cursor can't be closed

    probably the cursor is already declared and opened.
    try using following check.

    IF ( CURSOR_STATUS('global', 'c1') >= 0 OR CURSOR_STATUS('local', 'c1' ) >=
    0 )
    begin
    CLOSE c1
    deallocate c1
    end

    declare c1 cursor for select 1 from sysobjects

    open c1

    -Vishal

    "Shane" <com> wrote in message
    news:02b401c3628c$b257d530$gbl... 


    Vishal Guest

  3. #3

    Default Re: A cursor can't be closed

    Hi Shane,
    First of all, do not use the prefix SP_ for your stored procedures.
    The procedure will first be looked for in the Master database so this just
    slows down execution.
    SP_ should be reserved for System stored procedures only.

    To add the what Vishal wrote:
    I've had this same problem testing SPs in query yser.
    The problem is that if I declare a cursor and the SP is halted before the
    CLOSE and DEALLOCATE statements, the cursor stays open. If I modify my code
    and run it again, it tells me that the cursor already exists.
    I have to either add CLOSE and DEALLOCATE at the beginning of the SP
    (testing only, removing it when the SP executes completely), or close and
    reopen QA.

    "Shane" <com> wrote in message
    news:02b401c3628c$b257d530$gbl... 


    raydan Guest

Similar Threads

  1. Closed connection is being re-used
    By Eric in forum ASP.NET Web Services
    Replies: 3
    Last Post: May 19th, 09:11 AM
  2. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  3. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139