SqlServer Changes Cursor To Read Only

Jul 20, 2005

I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions. I
removed the Order-By clause from the statement to simplify it; it
gives the same error message with or without it.)

DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE

This gives the following error message: "FOR UPDATE cannot be
specified on a READ ONLY cursor."

I have tried a few different combinations of cursor types (like SCROLL
instead of FORWARD_ONLY) but they all give this error, although the
statement seems identical to what I have seen in the books and in
books online.

Any ideas on how to convert this into an updating cursor?

View 2 Replies

CURSOR READ ONLY

Apr 25, 2006

select * from new_iba_3

DECLARE new_cur2 CURSOR FOR select * from new_iba_3 order by rid, attr, val for update of rid;
open new_cur2

DECLARE @tracepoint NUMERIC,
@ibaidNUMERIC,
@ridNUMERIC,
@attrNUMERIC,
@valNVARCHAR(1024),
@src_ibaNUMERIC,
@src_linkNUMERIC,
@rowidNUMERIC,
@new_rec_ibaidNUMERIC,
@new_rec_ridNUMERIC,
@new_rec_attrNUMERIC,
@new_rec_valNVARCHAR(1024),
@new_rec_orig_ridNUMERIC

FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid
delete from new_iba_3 where current of new_cur2; --HERE

close new_cur2;
deallocate new_cur2;

select * from new_iba_3

WHEN I TRY TO DELETE FROM new_iba_3 IT GIVES CURSOR IS READ ONLY
BUT UPDATE WORKS ON THIS TABLE I HAVE NOT DECLARED THIS CURSOUR AS READ ONLY THEN WHY IT IS GIVING THIS ERROR

View 19 Replies View Related

Read The Csv In A Cursor Instead Of Bulk Update

Feb 13, 2008

Hello,
I am trying to read in from a csv file which works like this:

DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'

SET NOCOUNT ON

EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF

IF @doesExist = 1

BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')

What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?

View 5 Replies View Related

Does Cursor Convert Table To Read/write?

Apr 10, 2008

Hello,

Any help here much appreciated.

I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.

i suspect this is when i run a cursor through the table.

Is anyone able to confirm for me whether running a cursor changes a table's properties?

Many thanks.

Tim

Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.

View 3 Replies View Related

How Can I Read An Xml File From SQLServer

Mar 31, 2004

I need to know how to read an xml file using SQL. (in SQLServer)
Then i will manipulate the data and update my tables.

May be i can put the xml file in the IIS root.
Then what is required for the rest..

Thanks in advance

Benny

View 4 Replies View Related

CURSOR - Sqlserver - Good Example And Which Scenario We Wil Use

Feb 26, 2008

hi
Do you have any good example of CURSOR with sqlserver. When we should use?
Any live example will be appreciated, also how to call Cursor from .net Web Application
pls help to learn

View 2 Replies View Related

Read Text File From SqlServer

Apr 23, 2007

Hi Frinds,
I have the text file in my server.
Is it possible to read the text file and insert into table via sql server stored procedure.