Unforntunately we can't this break into smaller units that is what got us into trouble. We did have it into smaller procs in VFP but after a failure of the first one we need to roll back all changes. This is a critical procedure as it updates and creates pay entries for employees and updates employee bank balances.

A bank balance in my case is a value in time, such as annual leave. The industry we work with relies very heavily on time for banks..annual leave, sick time, long term sickness etc..

Stored Proc Summary
-----------------------------------------------
This process is very long but it only does some basic stuff.

1) Test to ensure we have record(s) to process
2) Determine if we need to create bank for this employee as he may have never had this bank before. (We have three bank files. tstoty1,2,3 = Time Sheet Totals Year x)
3) Open the Process Records cusro and then loop through each record and make an adjustment to the employees bank by means of credit or debit.
4) Obtain a transaction number from a control file
5) Insert the record after the adjustment has been made
6) If we have to adjust the differentials (Shift premiums - employees get extra pay for working the night shift) then automatically adjust the premiums or just return the set to the user to make manual adjustments.
7) Clean the table that we put records into from FoxPro(Transaction Record(s)).


Question
-----------------------------------------------
My question is should i chnage the transaction level for this process. It seems to be setting table locks instead of row locks...Is there something i can do. I am still new with SQL 7.0 so looking for this is a pain in the a$#. The books tell me that i can chnage this but the examples are not very good.

TIA
Shawn

>That's a pretty good sized proc. Could you provide the executive summary of what it's doing? I also noticed a couple of cursors. Do you have to step through one item at a time? Is there a set-oriented process that could be executed instead?
>
>Locks are held for the length of the transaction. Maybe you could break the work into smaller units and commit after each unit. Maybe 100 items.
>
>Maybe you could execute the proc during somekind of down- or slow time.
>
>-Mike