FOR UPDATE cannot be specified on a READ ONLY cursor
-
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
-
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.
-
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???
-
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
-
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.
-
I don't have a "READ_ONLY" in there though... hence my confusion.
-- Stephen Cook
-
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.
-
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
-
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.
-
Yeah..., I did kind of sidetrack us. I agree with RGR'us.
I wasn't born stupid - I had to study.
-
Are you sure this is posted in the right thread??
-
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
-
Is the number of rows/page gonna be dynamic at some point or will remain static. How large is that large dataset?
-
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
-
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