Professional Web Applications Themes

Error : The cursor is READ ONLY. - Microsoft SQL / MS SQL Server

In my stored procedure i have a cursor for a table A which loop through rows of A one by one. For each row, I update some other tables based on the information and then delete that row from table A. Problem is I cant delete the row from table A. I get this error message : Server: Msg 16929, Level 16, State 1, Procedure cst_import_pos_hr, Line 974 The cursor is READ ONLY. DECLARE c_import CURSOR FOR SELECT employee_id, user_def, user_value FROM A OPEN c_import FETCH FROM c_import INTO v_emp_id, v_user_def, v_user_value WHILE FETCH_STATUS = 0 BEGIN ... DELETE FROM ...

  1. #1

    Default Error : The cursor is READ ONLY.

    In my stored procedure i have a cursor for a table A which loop through rows
    of A one by one. For each row, I update some other tables based on the
    information and then delete that row from table A. Problem is I cant delete
    the row from table A. I get this error message :
    Server: Msg 16929, Level 16, State 1, Procedure cst_import_pos_hr, Line 974
    The cursor is READ ONLY.

    DECLARE c_import CURSOR FOR SELECT employee_id, user_def, user_value FROM A
    OPEN c_import
    FETCH FROM c_import INTO v_emp_id, v_user_def, v_user_value
    WHILE FETCH_STATUS = 0
    BEGIN
    ...
    DELETE FROM A WHERE CURRENT OF c_import
    FETCH FROM c_import INTO v_emp_id, v_user_def, v_user_value
    END
    CLOSE c_import
    DEALLOCATE c_import

    I have another stored procedure which is doing the same thing for table B.
    And it is working perfect. Can anybody help me please? Thanks


    Rizwan Guest

  2. #2

    Default Re: Error : The cursor is READ ONLY.

    Can anybody answer this question please? Thanks

    "Rizwan" <com> wrote in message
    news:Pz9Ya.193$bellglobal.com... 
    rows 
    delete 
    974 



    Rizwan Guest

  3. #3

    Default Re: Error : The cursor is READ ONLY.

    change to

    DECLARE c_import CURSOR
    FOR SELECT employee_id, user_def, user_value FROM A
    FOR UPDATE

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Rizwan" <com> wrote in message
    news:Pz9Ya.193$bellglobal.com... 
    rows 
    delete 
    974 



    oj Guest

  4. #4

    Default Re: Error : The cursor is READ ONLY.

    hi oj,
    I made the change but now i am getting a new error message:
    Server: Msg 16957, Level 16, State 4, Procedure cst_import_pos_hr, Line 269
    FOR UPDATE cannot be specified on a READ ONLY cursor.

    One thing I forget to mention is that there is an ORDER BY clause in the
    SELECT statement. The statement is :
    DECLARE c_import CURSOR FOR SELECT employee_id, user_def, user_value FROM A
    ORDER BY employee_id

    Do you think thats the reason? Thanks


    "oj" <com> wrote in message
    news:phx.gbl... 
    > rows 
    > delete 
    > 974 [/ref]
    FROM [/ref]
    B. 
    >
    >[/ref]


    Rizwan Guest

  5. #5

    Default Re: Error : The cursor is READ ONLY.

    >> One thing I forget to mention is that there is an ORDER BY clause in the
    SELECT statement..... Do you think thats the reason? <<

    It is a possibility, depending on any indexes on the column employee_id. For
    details on this issue refer to the topic Implicit Cursor Conversion in SQL
    Server Books Online ( acdata.chm:: /ac_8_con_07_66sz.htm )

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  6. #6

    Default Re: Error : The cursor is READ ONLY.

    Try...

    DECLARE c_import CURSOR KEYSET
    FOR SELECT employee_id, user_def, user_value FROM A
    ORDER BY employee_id
    FOR UPDATE

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Rizwan" <com> wrote in message
    news:9feYa.1520$bellglobal.com... 
    269 
    A [/ref][/ref]
    through 
    > > delete [/ref][/ref]
    Line [/ref]
    > FROM [/ref][/ref]
    table 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

Similar Threads

  1. Replies: 0
    Last Post: March 29th, 09:49 PM
  2. cursor already in use error
    By Sim in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: March 2nd, 10:03 AM
  3. error using cursor
    By JohnB in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 1st, 10:37 PM
  4. error: Incorrect database or cursor name format
    By Philipp Walderdorff in forum Informix
    Replies: 2
    Last Post: July 27th, 04:38 AM
  5. Replies: 0
    Last Post: July 16th, 07:00 AM

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