>My question is should i chnage the transaction level for this process.

The query processor determines the granularity of locking both at the time of optimization and when the query is being execute. In general, SQL Server is very biased toward row locks but the locking can be escalated to a less granular level (pages or tables) as the engine begins applying locks. You see, locks are a finite resource so SQL Server will try to minimize their usage.

The recommended practice is allow SQL Server to determine and apply the locks has it feels necessary.

Now, you can use the Server Enterprise Manager and the system stored procedure sp_lock to determine what locks are being held by the system. If there are some tables where Shared locks are being applied and these locks are causing other SPIDs to block, you might be able to use a Table Hint and instruct the query processor to NOT apply the Shared locks.

Understand that I'm not recommending that this is the route that you take. It is just an option that is available to you.

Now regarding the operations with the stored proc, are there any that cannot be done in a set-oriented manner? Do you really need the cursor? Is the retrival of the transaction number from the control file a one-time operation or is it done multiple times within the process?

-Mike