Can you write it without using a cursor? The cursor has
optimistic locking so if the underlying data changes, this
will not be reflected within the cursor and the update
will fail. I would do what ever it takes to implement the
solution without a cursor. That way you can wrap all the
code within a transaction and include error handling with
little degradation to performance.

Mark Baekdal
[url]www.dbghost.com[/url]
>-----Original Message-----
>Hi SQL Gurus,
>I have a big batch process in a stored procedure, i.e : a
payroll
>process to calculate salary and all allowance should be
received by an
>employee.
>Ramdomly I get this error :
>"Optimistic concurrency check failed. The row was
modified outside of
>this cursor."
>
>Here is the outline, what is wrong here ?
>
>CREATE PROC P_Payroll(..parameters..)
>As
>DECLARE....
>BEGIN TRAN
>SET MyCursor = CURSOR FOR SELECT .... FROM EMPLOYEE
> WHERE <condition>
>OPEN MyCursor
>FETCH NEXT FROM MyCursor INTO EMPNO,...,...
>WHILE FETCH_STATUS=0
>BEGIN
> ..<calculate basic salary>...
> Insert <basic salary> Into Payroll_Summary ....
> IF ERROR <> 0
> GOTO Fail
>
> ..<calculate Meal allowance>...
> Insert <Meal allowance> Into Payroll_Summary ....
> IF ERROR <> 0
> GOTO Fail
>
> ...
> ... <Do the same with Tax, Bonus, etc...>
>
> -- Finally
> UPDATE EMPLOYEE SET LastProcessDate=ProcessDate
> WHERE Current Of MyCursor
> IF ERROR <> 0
> GOTO Fail
>
> FETCH NEXT FROM MyCursor INTO EMPNO,...,...
>END
>CLOSE MyCursor
>DEALLOCATE MyCursor
>
>Success:
>COMMIT TRAN
>GOTO Finish
>
>Fail:
>ROLLBACK TRAN
>
>Finish:
>GO
>
>Thanks in advance for your help,
>Krist
>
>
>
>
>*** Sent via Developersdex [url]http://www.developersdex.com[/url]
***
>Don't just participate in USENET...get rewarded for it!
>.
>