Very Computer

I know that even if you have nested Begin and Commit trans, rollback
will roll back everything within the outermost begin commit.

I am processing about 200,000 records so I wanted to commit every 1000,
and I have a construct like the following:

Counter=0
Begin tran
start while loop
    counter=counter+1

    if counter=1000
        commit tran
        counter = 0
        begin tran
   endif

  ---process records
end while
commit tran

I expected it to commit for every 1000 records and start a new
transaction for next 1000. My procedure bombed and it seems it is
rolling back all the records. It is meant to be that way in SQL, has
anyone tried commiting by parts.

For me, since I am commiting and starting a new transaction, it is not
nested transaction. Because commit comes before another begin. Why isn't
it working the way it is supposed?

--
Shailesh Kh*

Seems like optimizer trying to build too largge plan for your proc.
Split it into 2 procedures one for external loop and one which
begin tran, loop 1000 records and commit them.
This should work.
Also you may find out that you need to give time for checkpoint to do it
job.
For this purpose you may call waitfor delay between your transactions.

-------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer

http://www.veryComputer.com/~shleym
SQL Answers located at
http://www.veryComputer.com/~shleym/sql_answers.htm

---------------

I need to do it in the same procedure because the processing logic is quite
complex so I can't branch out to an external procedure.

The checkpoint only writes the pages in memory to disk, how does it matter
for rollback? My question was whether my logic is fine for splitting the
transaction and commiting every 1000 records.

Thanks

Are you absolutely sure there is no existing transaction ?

--
Hope the above helps
-----------------------------------
Tony Rogerson MCP / SQL Server MVP
Torver Computer Consultants Ltd.
London, England

Quote:>I know that even if you have nested Begin and Commit trans, rollback
>will roll back everything within the outermost begin commit.

>I am processing about 200,000 records so I wanted to commit every 1000,
>and I have a construct like the following:

>Counter=0
>Begin tran
>start while loop
>    counter=counter+1

>    if counter=1000
>        commit tran
>        counter = 0
>        begin tran
>   endif

>  ---process records
>end while
>commit tran

>I expected it to commit for every 1000 records and start a new
>transaction for next 1000. My procedure bombed and it seems it is
>rolling back all the records. It is meant to be that way in SQL, has
>anyone tried commiting by parts.

>For me, since I am commiting and starting a new transaction, it is not
>nested transaction. Because commit comes before another begin. Why isn't
>it working the way it is supposed?

>--
>Shailesh Kh*