Professional Web Applications Themes

Need an advice on 'declare cursor' statement - Microsoft SQL / MS SQL Server

Hi all I run next declare cursor statement: declare c1 cursor for select date_modified, event_modified_cd, rec_id from physicalsecurity order by date_modified, event_modified_cd, rec_id for update of date_modified, event_modified_cd, rec_id .... and I am getting next error: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor. I tried different options like 'keyset' or 'scroll' - the same simple problem. Does anyone know what I am doing wrong? Thank you, Gene....

  1. #1

    Default Need an advice on 'declare cursor' statement

    Hi all

    I run next declare cursor statement:

    declare c1 cursor
    for
    select date_modified, event_modified_cd, rec_id
    from physicalsecurity
    order by date_modified, event_modified_cd, rec_id
    for update of date_modified, event_modified_cd, rec_id

    .... and I am getting next error:

    Server: Msg 16957, Level 16, State 4, Line 3
    FOR UPDATE cannot be specified on a READ ONLY cursor.

    I tried different options like 'keyset' or 'scroll' - the
    same simple problem.
    Does anyone know what I am doing wrong?

    Thank you, Gene.
    gene Guest

  2. #2

    Default Need an advice on 'declare cursor' statement

    It seems that you're missing other necessary components in
    the cursor command, e.g. FETCH. Please check SQL
    BookOnline.
    >-----Original Message-----
    >Hi all
    >
    >I run next declare cursor statement:
    >
    >declare c1 cursor
    >for
    >select date_modified, event_modified_cd, rec_id
    >from physicalsecurity
    >order by date_modified, event_modified_cd, rec_id
    >for update of date_modified, event_modified_cd, rec_id
    >
    >.... and I am getting next error:
    >
    >Server: Msg 16957, Level 16, State 4, Line 3
    >FOR UPDATE cannot be specified on a READ ONLY cursor.
    >
    >I tried different options like 'keyset' or 'scroll' - the
    >same simple problem.
    >Does anyone know what I am doing wrong?
    >
    >Thank you, Gene.
    >.
    >
    Jane Guest

  3. #3

    Default Re: Need an advice on 'declare cursor' statement

    A cursor containg an ORDER BY is automatically read-only, But you can always do updates with a where clause rather than "current of cursor" to get round it.

    Even better lose the cursor and do the process in one statement. Can you post the whole problem and see if someone can remove the need for the cursor?

    Mike John

    "gene" <gene_golubhotmail.com> wrote in message news:03f401c3471f$fd4f0b40$a301280aphx.gbl...
    > Hi all
    >
    > I run next declare cursor statement:
    >
    > declare c1 cursor
    > for
    > select date_modified, event_modified_cd, rec_id
    > from physicalsecurity
    > order by date_modified, event_modified_cd, rec_id
    > for update of date_modified, event_modified_cd, rec_id
    >
    > ... and I am getting next error:
    >
    > Server: Msg 16957, Level 16, State 4, Line 3
    > FOR UPDATE cannot be specified on a READ ONLY cursor.
    >
    > I tried different options like 'keyset' or 'scroll' - the
    > same simple problem.
    > Does anyone know what I am doing wrong?
    >
    > Thank you, Gene.
    Mike John Guest

  4. #4

    Default Re: Need an advice on 'declare cursor' statement

    Thank you Mike

    Yes, it's always 'get around' exists.
    I had to remove duplicate rows from table and it seemed
    the most elegant solution.
    I will do that without using cursor.

    Thank you for your tip on ordered cursors. I never knew
    that.

    Gene.

    >-----Original Message-----
    >A cursor containg an ORDER BY is automatically read-only,
    But you can always do updates with a where clause rather
    than "current of cursor" to get round it.
    >
    >Even better lose the cursor and do the process in one
    statement. Can you post the whole problem and see if
    someone can remove the need for the cursor?
    >
    >Mike John
    >
    >"gene" <gene_golubhotmail.com> wrote in message
    news:03f401c3471f$fd4f0b40$a301280aphx.gbl...
    >> Hi all
    >>
    >> I run next declare cursor statement:
    >>
    >> declare c1 cursor
    >> for
    >> select date_modified, event_modified_cd, rec_id
    >> from physicalsecurity
    >> order by date_modified, event_modified_cd, rec_id
    >> for update of date_modified, event_modified_cd, rec_id
    >>
    >> ... and I am getting next error:
    >>
    >> Server: Msg 16957, Level 16, State 4, Line 3
    >> FOR UPDATE cannot be specified on a READ ONLY cursor.
    >>
    >> I tried different options like 'keyset' or 'scroll' -
    the
    >> same simple problem.
    >> Does anyone know what I am doing wrong?
    >>
    >> Thank you, Gene.
    >
    >.
    >
    gene Guest

  5. #5

    Default Need an advice on 'declare cursor' statement

    Thank you Jane for your respond

    Other fetch statements were there but I did not even get
    to them in a code.
    The problem was that 'order by' in a declare cursor makes
    this cursor automaticaly 'read only'.
    That's were my problem lied.

    Thanks again, Gene.
    >-----Original Message-----
    >It seems that you're missing other necessary components
    in
    >the cursor command, e.g. FETCH. Please check SQL
    >BookOnline.
    >
    >>-----Original Message-----
    >>Hi all
    >>
    >>I run next declare cursor statement:
    >>
    >>declare c1 cursor
    >>for
    >>select date_modified, event_modified_cd, rec_id
    >>from physicalsecurity
    >>order by date_modified, event_modified_cd, rec_id
    >>for update of date_modified, event_modified_cd, rec_id
    >>
    >>.... and I am getting next error:
    >>
    >>Server: Msg 16957, Level 16, State 4, Line 3
    >>FOR UPDATE cannot be specified on a READ ONLY cursor.
    >>
    >>I tried different options like 'keyset' or 'scroll' - the
    >>same simple problem.
    >>Does anyone know what I am doing wrong?
    >>
    >>Thank you, Gene.
    >>.
    >>
    >.
    >
    gene Guest

Similar Threads

  1. how to declare the CURSOR in mysql
    By zhongdai in forum MySQL
    Replies: 1
    Last Post: March 2nd, 02:24 AM
  2. SQL Statement BETWEEN 2 Dates - Advice Please.
    By TomT in forum ASP Database
    Replies: 14
    Last Post: August 12th, 05:04 PM
  3. Replies: 1
    Last Post: August 24th, 03:45 PM
  4. 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
  5. re-declare (re-use) a cursor in a stored procedure
    By Joe Celko in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 08:37 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