Cursor Error during SP execution.....

• M Jones

  SSCommitted

  Points: 1711

  More actions

  September 8, 2004 at 11:34 am

  #59416

  HELP

   

  I am trying to test execution of newly coded Stored procedures.  Of course I get an error on the first one!!!  ARRRRGGGGG....

  Here is how my cursor is defined:

  DECLARE sort_cursor CURSOR

    FOR SELECT FULLORDERNO, WAREHOUSELOC

          FROM @mkfile1

          ORDER BY SUBSTRING(DIVISION,1,1),

                        FULLORDERNO,

                        ZONECOMBO,

                        WAREHOUSELOC

    FOR UPDATE OF ZONECOMBO

  Syntax is correct but when executed I am getting the following error message and I don't know how to correct it.  This should not be a READ ONLY cursor.

  Server: Msg 16957, Level 16, State 4, Procedure mk_pld_sort1, Line 98

  FOR UPDATE cannot be specified on a READ ONLY cursor.

   

  Is there a setting somewhere I need to know about or something?  I have several SP's with similar coding and I need to be able to use and updatable cursor.

  

  Please HELP!  Thanks!

  

  ~mj

• September 13, 2004 at 8:00 am

  This was removed by the editor as SPAM

• olavho

  Say Hey Kid

  Points: 706

  More actions

  September 13, 2004 at 10:37 am

  #522800

  The problem is most likely the order-by-clause. Templeton gave an explanation to this in this post:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=134851#bm134903

  One possible work-around might be to insert the records into variable @mkfile1 using order-by, or perhaps create another variable and do:

  insert @mkfile2

  select * from @mkfile1

  order by substring(division, 1,1),

  fullorderno,

  zonecombo,

  warehouseloc

  , and use the new variable as basis for the cursor.