Post reply

FOR UPDATE cannot be specified on a READ ONLY cursor

  • October 20, 2005 at 9:12 am

    #89793

    For some reason I am getting the error "FOR UPDATE cannot be specified on a READ ONLY cursor." Does anyone know what could cause such a thing?

    I have full access to the table it references and all that. I'm thinking there is some detail that I have missed and cannot find in BOL.

    -- Stephen Cook

  • October 20, 2005 at 9:27 am

    #598761

    Post the code for the Cursor, it may be declared as non-compliant with what you want to do.  (My guess is, you may not need a Cursor). 

     

    I wasn't born stupid - I had to study.

  • October 20, 2005 at 9:32 am

    #598765

    Not needing a cursor is most likely true but I'm gonna state the obvious : How can you declare a cursor for update that can only read the data???

  • October 20, 2005 at 9:38 am

    #598769

    I have a table (dumped from a spreadsheet) for which I must generate a report with one doctor per page, but with six or fewer of his patients per page. So I am trying to divide the patients up into pages.

    CREATE TABLE AuthFormData

    (

    MedicareID CHAR(16),

    LastName VARCHAR(32),

    FirstName VARCHAR(32),

    MI VARCHAR(5),

    Sex CHAR(1),

    DOB DATETIME,

    UPIN CHAR(6),

    PCPLastName VARCHAR(32),

    PCPFirstName VARCHAR(32),

    PCP_MI_Title VARCHAR(10),

    PCPAddress1 VARCHAR(100),

    PCPAddress2 VARCHAR(100),

    PCPCity VARCHAR(50),

    PCPState CHAR(5),

    PCPZip CHAR(15),

    PCPPhone CHAR(15),

    PCPFax CHAR(15),

    PAGE INT

    )

    DECLARE csr CURSOR DYNAMIC FOR

    SELECT PCPLastName,

    PCPFirstName,

    PCP_MI_Title,

    PAGE

    FROM AuthFormData

    ORDER BY PCPLastName,

    PCPFirstName,

    PCP_MI_Title,

    LastName,

    FirstName,

    MI

    FOR UPDATE OF PAGE

    Then I (try to) go on to counting records for each PCP and assigning page numbers to the patients with a WHERE CURRENT OF in my UPDATE.

    I couldn't think of a way to do it set-based... so if there is one I'd be very interested in learning how!

    RGR'us: I didn't tell it to be read-only

    -- Stephen Cook

  • October 20, 2005 at 9:40 am

    #598773

    DECLARE CURSOR

    Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

    SQL-92 Syntax

    DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

    FOR select_statement

    [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

    Transact-SQL Extended Syntax

    DECLARE cursor_name CURSOR

    [ LOCAL | GLOBAL ]

    [ FORWARD_ONLY | SCROLL ]

    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

    [ TYPE_WARNING ]

    FOR select_statement

    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

    I wasn't born stupid - I had to study.

  • October 20, 2005 at 9:49 am

    #598778

    I don't have a "READ_ONLY" in there though... hence my confusion.

    -- Stephen Cook

  • October 20, 2005 at 9:54 am

    #598785

    Why did you chose Dynamic?  Try Fast_Forward and see if it works....

    (made a quick read about Dynamic in BOL and not sure if the Commit Transaction is causing the problem - but gotta run out of the office for a bit...). 

    Good luck 

     

    I wasn't born stupid - I had to study.

  • October 20, 2005 at 9:57 am

    #598791

    That gets the error "Conflicting cursor options FAST_FORWARD and FOR UPDATE."

    Don't you need DYNAMIC to update the records fetched?

    I an not using transactions for this.

    -- Stephen Cook

  • October 20, 2005 at 9:58 am

    #598795

    Back to the original solution... please post some sample data and the expected output so that we can show you the set based solution to this problem.

  • October 20, 2005 at 1:50 pm

    #598918

    Yeah..., I did kind of sidetrack us.  I agree with RGR'us. 

     

    I wasn't born stupid - I had to study.

  • October 21, 2005 at 12:23 pm

    #599252

    Are you sure this is posted in the right thread??

  • October 21, 2005 at 12:44 pm

    #599258

    I cannot post sample data or expected results because of the HIPAA law, but I can offer a simplified fictional version.

    Doctor Patient PAGE
    Alice Arnold 1
    Alice Bob 1
    Betty Alfred 1
    Betty Bernard 1
    Betty Carl 1
    Betty Doug 1
    Betty Ed 1
    Betty Fred 1
    Betty Gerald 2
    Betty Harold 2
    Cindy Abelard 1
    Cindy Bubba 1

    Basically, for each doctor (there can be 1 to n rows for each doctor), I need to takes groups of six records and give them a page number that is unique within that doctor's rows.

    The table is the same as the output, except the PAGE field would be filled in (hence the update).

    I worked around it by UPDATEing a row at a time (matching the row with the WHERE clause), but that just kind of twists the knife that was stuck in me when I had to use a cursor in the first place.

    Still, I got word that this will soon become a recurring report, with a much larger dataset, so any advice would be appreciated.

    -- Stephen Cook

  • October 21, 2005 at 12:50 pm

    #599262

    Is the number of rows/page gonna be dynamic at some point or will remain static. How large is that large dataset?

  • October 21, 2005 at 12:56 pm

    #599264

    rows and pages will be dynamic, it all depends on how many doctors get flagged to be reported, and how many patients they have. the dataset with be randomly sized as well, but could be up to 50k records (ok, that isn't LARGE, but it will be pretty slow to process with my current solution).

    -- Stephen Cook

  • October 21, 2005 at 12:59 pm

    #599267

    Search the forums for the word paging... there was a link posted for a similar problem a few days ago that would surely help you.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply