I write a SQL which use cursor to loop and update records.
There are 500 records to be updated. I run the sql in
Query Analyser and it works. Then I place it as one of the
tasks in DTS with transaction and then I run the DTS, it
reported successful. But when I looked at the data, only
about 300 records are updated. It seems that after
updating about 300 records, it suddenly move to the next
task without reporting any problem. This problem can be
replicated after I reboot the machine.

Folks, any idea or suggestion? Thanks...

There are few things you can try:

1) if possible rid of cursors and take advantage of set processing

2) if cursors are truly needed replace them with pseudo-cursors

3) wrap it all up into a stored procedure

In most of the cases cursors are used they are not necessary. They make the
code less efficient and harder to read and maintain. You can find tons of
articles on the topic.

Ilya



"Jack" <jack (AT) discussions (DOT) microsoft.com> wrote

Thanks a lot, wrapping it to a sproc works!