Results 1 to 3 of 3

Thread: Partial updates by cursor

  1. #1
    Balajee Subramanian Guest

    Partial updates by cursor

    I am are running SQLSERVER SP4 on WINNT SP3.

    I am serious problem of partial update my query is
    something like
    bEGIN tRAN
    declare cursor...

    SElect * from tableA where flag = null

    open cursor
    fetch first...

    while @@FETCH_STATUS = 0
    BEGIN
    UPDATE TABLE tableB ..
    .
    .
    .

    fetch next
    END
    CLOSE ...
    DEALLOCATE..
    COMMIT TRAN

    The problem is the cursor select retrieves
    says about 10000 rows, goes thru the
    loop for 1000 rows and just terminates without
    giving an error message,or rolling back
    in case of errors, but comes out as successfully
    completed.


    I am at loss as what could be the problems..
    any suggestions welcome..

    Thanks in advance,
    Balajee.








  2. #2
    HARDBALL Guest

    Partial updates by cursor (reply)

    Try this.....

    DECLARE cursor....
    OPEN cursor
    FETCH NEXT FROMcursor INTO ....
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    ..........
    END
    else print `**************** MISSING VALUE **********************`
    FETCH NEXT FROM mycursor INTO @x
    END
    CLOSE cursor
    DEALLOCATE cursor
    GO
    On 9/18/98 4:52:28 AM, Balajee Subramanian wrote:
    > I am are running SQLSERVER SP4 on WINNT SP3.

    I am serious problem of
    > partial update my query is
    something like
    bEGIN tRAN
    declare
    > cursor...

    SElect * from tableA where flag = null

    open cursor
    fetch
    > first...

    while @@FETCH_STATUS = 0
    BEGIN
    UPDATE TABLE tableB
    > ..
    .
    .
    .

    fetch next
    END
    CLOSE ...
    DEALLOCATE..
    COMMIT TRAN

    The
    > problem is the cursor select retrieves
    says about 10000 rows, goes thru
    > the
    loop for 1000 rows and just terminates without
    giving an error
    > message,or rolling back
    in case of errors, but comes out as
    > successfully
    completed.


    I am at loss as what could be the
    > problems..
    any suggestions welcome..

    Thanks in advance,
    Balajee.








  3. #3
    Anthony B. Kenitzki Guest

    better fix- Re: Partial updates by cursor



    Here`s a hint:

    declare your cursor to be INSENSITIVE.

    This way, every record in your cursor query set is processed only once. If you don`t use an insensitive cursor, a row can be updated and added to the end of the cursor list - repeatedly - this explains your 10,000 selected rows problem. You have this problem because SQL Server update statements are sometimes processed as DELETE/INSERT pairs in the transaction log. (Delete the record with the old values and insert it with the new.) When the updated record is re-inserted into the table it appears at the end of the cursor select list. INSENSITIVE cursors use a temporary table separate from the table that is being modified.

    Try this:

    declare foo INSENSITIVE cursor for select bar
    open foo
    fetch foo into @blah
    while @@fetch.... begin
    begin transaction
    update...
    commit transaction
    fetch foo into @blah
    end
    close foo
    deallocate foo

    This should solve your problems.

    Anthony B. Kenitzki


    On 9/18/98 4:52:28 AM, Balajee Subramanian wrote:
    > I am are running SQLSERVER SP4 on WINNT SP3.

    I am serious problem of
    > partial update my query is
    something like
    bEGIN tRAN
    declare
    > cursor...

    SElect * from tableA where flag = null

    open cursor
    fetch
    > first...

    while @@FETCH_STATUS = 0
    BEGIN
    UPDATE TABLE tableB
    > ..
    .
    .
    .

    fetch next
    END
    CLOSE ...
    DEALLOCATE..
    COMMIT TRAN

    The
    > problem is the cursor select retrieves
    says about 10000 rows, goes thru
    > the
    loop for 1000 rows and just terminates without
    giving an error
    > message,or rolling back
    in case of errors, but comes out as
    > successfully
    completed.


    I am at loss as what could be the
    > problems..
    any suggestions welcome..

    Thanks in advance,
    Balajee.








Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •