SQL Cursor problem

D

Darth Bagel

Limp Gawd
Joined
Feb 1, 2005
Messages
383
I'm having trouble with a cursor in a project I'm working on. I try to declare it as being for update, and SQL server claims that it's a read-only cursor and therefore can't be declared for update. So far I've had the DBA and another programmer look at it, and we haven't found anything. I'm using this code to declare the cursor:

Code: 
DECLARE trades_cursor CURSOR 
FOR
SELECT fncode,symbol,trxnum,sacode,ttcode,qty,stcode,brcode,basketid FROM #trades ORDER BY fncode, sacode, symbol, trddate
FOR UPDATE OF startqty, qty, endqty, errorflag
 
The columns specified in the FOR UPDATE OF clause all need to appear in the SELECT statement. EDIT: Maybe... SQL Server Books Online isn't terribly clear if this is the case or not. EDIT 2: After testing, this is the error message I got "The cursor has a FOR UPDATE list and the requested column to be updated is not in this list." Therefore, my initial statement is correct.
 
An additional issue exists if #trades does not have a unique index (which is likely, if it doesn't have a primary key defined on it.) In such case, you get an implicit conversion to a static cursor, which is incapable of being updated.
 
Adding a primary key constraint to #trades fixed it. Thanks for the help.

Out of curiosity, you seem to be answering most SQL questions in this forum, do you know this stuff off the top of your head or are you getting the information from someplace in particular?
 
You're welcome. :cool:

A lot of things I remember; in other cases, I know how to dig info up without much effort. I've taken a couple database courses in the course of my education, one undergrad and one grad. Both courses used Oracle as the DBMS for homework and projects. I'm self-taught on SQL Server, starting with 6.0, followed by a transition to 2000. Initially, learning about SQL Server was a matter of being able to make minor improvements to existing systems, but ultimately evolved into a necessity for developing another system. I've found SQL Server Books Online to be a useful, though not always comprehensive resource. MSDN contains additional info, and Google helps to dig up nuggets of insight from others. Experimentation is also key, as sometimes what you believe to be true proves not to be, and documentation isn't always clear on things. SQL Server is the DDMS that, by far, I have the most experience with, though I can also cover generic SQL issues. I don't have biases against other DBMSs in general, it's just a matter of focusing on the best tool available to me in my current position. If at some later time I have reason to use a different DBMS, then I'll learn it on the fly.

On another note, if your employer has / develops a need for someone of my skills, feel free to point them in my direction. While I haven't begun to job hunt in earnest, I'm certainly open to a change.
 
You must log in or register to reply here.
Back
Top