-
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.
-
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.
-
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
-
Forum Rules
|
|